• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 508
  • Last Modified:

How do I copy the same formula into the full range eg column as selected by code.

I want to copy a formula into a column next to data that has been copied and pasted using the code in item A. Can anyone correct the code below items B, C & D.
The code in item B,C & D only copies the formulae in the first two rows but item A, places the data for the full list of data which has been copied from sheet "rngSrc".

A)   rngDst.Offset(LastRow, 5).PasteSpecial xlPasteValuesAndNumberFormats


B)   rngDst.Offset(LastRow, 0).FormulaR1C1 = "=EmployeeData!R2C1"
C)   rngDst.Offset(LastRow, 1).FormulaR1C1 = "=TEXT(EmployeeData!R2C2,""yyyy"")"
D)   rngDst.Offset(LastRow, 2).FormulaR1C1 = "=TEXT(EmployeeData!R2C2,""mmmm"")"
0
user2073
Asked:
user2073
  • 5
  • 5
1 Solution
 
nutschCommented:
Can you give us the line where you specify the range to copy? It would help.

Otherwise, you can try

dim lLastRow as long

lLastRow=cells(rows.count,rngDst.Offset(LastRow, 5).column).end(xlup).row

range(rngDst.Offset(LastRow, 0),cells(llastrow,rngDst.Offset(LastRow, 0).column)).FormulaR1C1 = "=EmployeeData!R2C1"

range(rngDst.Offset(LastRow, 1),cells(llastrow,rngDst.Offset(LastRow, 1).column)).FormulaR1C1 = "=TEXT(EmployeeData!R2C2,""yyyy"")"

range(rngDst.Offset(LastRow, 2),cells(llastrow,rngDst.Offset(LastRow, 2).column)).FormulaR1C1 = "=TEXT(EmployeeData!R2C2,""mmmm"")"

Open in new window


Thomas
0
 
user2073Author Commented:
I hope the attached file provides sufficient code to show the processes currently used.
Code-LastRow.docx
0
 
user2073Author Commented:
When I insert your code it stops running when it gets to line shown:

Range(rngDst.Offset(LastRow, 0), Cells(LastRow1, rngDst.Offset(LastRow, 0).Column)).FormulaR1C1 = "=EmployeeData!R2C1"

Can you help?

THANKS
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
nutschCommented:
What is the range that you're copying when you run that line

rngDst.Offset(LastRow, 5).PasteSpecial xlPasteValuesAndNumberFormats
0
 
user2073Author Commented:
The range would be column (5) the number of rows at this time is 215 rows. The column number varies because as the macro copies single rows at a time to relocation another worksheet "rngDst" from "rngSrc". Each column is not in the same order and not all columns are copied. The number of Rows are only the same, while running the macro the next time I run the macro te number of rows could be more or less depending on the "rngSrc" at the time.
The current macro is working OK, except for a couple of problems.
   The problems that exist are:
     1) I would like to place a formula in each cell in the column selected.
     2) Also, change the formatting and alignment information in each column selected.

Currently, the formula works as it copies the formula fine but does not fill the column for the length of the range "rngSrc".

I hope this helps. THANKS
0
 
nutschCommented:
try

rngDst.Offset(LastRow, 0).resize(rngsrc.rows.count).FormulaR1C1 = "=EmployeeData!R2C1"

Open in new window

0
 
user2073Author Commented:
Your code is working, thank you.

Using the same code how can I apply formating to the same cells.
In some situations I need to change the alignment and/or in the case of numbers I need to change the number of decimal points used. Can you help, Please.
0
 
nutschCommented:
something like

with rngDst.Offset(LastRow, 0).resize(rngsrc.rows.count)
.FormulaR1C1 = "=EmployeeData!R2C1"
.numberformat="0.00"
.HorizontalAlignment = xlLeft
end with

Open in new window

0
 
user2073Author Commented:
Your code works fantastic, Thanks.
I'll be using your code.

Below is some code that I had just tested based on your earlier code which works also.

rngDst.Offset(LastRow, 8).PasteSpecial xlPasteValues
rngDst.Offset(LastRow, 8).Resize(rngSrc.Rows.Count).NumberFormat = "0.0"


You guys are appreciated because without you I would not develope better skills.

THANKS
0
 
nutschCommented:
Glad to know you're learning. I keep on learning from being on EE too. One thing though: to help you get better answers here, try answering questions precisely. Otherwise, things can get frustrating on the expert side (I'm not particularly renowned for my patience :-P).

See you in a next question,

Thomas
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now