UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Copy A Formula Down A Column Just Down To The Last Row Of Data, Office 2013    
 
   
baffled100
post Jun 13 2018, 02:57 PM
Post#1



Posts: 390
Joined: 10-December 12



Hi,

I have a macro in Excel that updates a column of numbers to be percentages in a file. For example, the Excel file has data in columns A-F and rows 1-50. (The number of rows in this file will vary, but the columns stay the same.) My macro creates a formula in G2 that takes the number in F2 and divides by 10. I then need to copy the formula as far down as the last row of data (which varies with each file). I then need to copy the contents of the cells in column G to column F, but only as far down as the last row of data. (I use the Paste values of the Paste special command.

I have two problems with my macro that I need to figure out how to fix. The first is how can I only copy the formula in column G down as far as the last row of data which varies? I haven't been able to get the Range(Selection, Selection.End(xlDown)).Select to work. The other problem is the column of numbers (F) has some cells that are blank and need to stay blank. They cannot turn into 0's.

Any help is greatly appreciated!
Go to the top of the page
 
theDBguy
post Jun 13 2018, 03:03 PM
Post#2


Access Wiki and Forums Moderator
Posts: 73,504
Joined: 19-June 07
From: SunnySandyEggo


Hi,

According to this website, there are several ways to find the last row of data.

Hope it helps...
Go to the top of the page
 
baffled100
post Jun 13 2018, 04:26 PM
Post#3



Posts: 390
Joined: 10-December 12



Thanks very much for your reply. I'm not sure how to use the information though....if I were to use this option:

LastRow = ActiveSheet.UsedRange.Rows.Count

What would be the command to copy the formula from G2 down to the last row?

Thank you!
Go to the top of the page
 
theDBguy
post Jun 13 2018, 04:31 PM
Post#4


Access Wiki and Forums Moderator
Posts: 73,504
Joined: 19-June 07
From: SunnySandyEggo


Oh, I thought you already had that and just needed to account for the dynamic row count. In any case, try using the .FillDown method of the Range object.

Hope it helps...
Go to the top of the page
 
baffled100
post Jun 13 2018, 05:52 PM
Post#5



Posts: 390
Joined: 10-December 12



Thanks, I did figure that out.......but I still need to figure out how to leave the blank fields (in col F) blank. The formula I am using changes the blanks to 0's but there are some entries that are in fact 0 and need to be able to be identified as such........thanks!
Go to the top of the page
 
theDBguy
post Jun 13 2018, 06:36 PM
Post#6


Access Wiki and Forums Moderator
Posts: 73,504
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Does Excel have a function called ISBLANK()? How about ISEMPTY()? If not, you could probably use LEN().

Sent from phone...
Go to the top of the page
 
baffled100
post Jun 13 2018, 07:34 PM
Post#7



Posts: 390
Joined: 10-December 12



Thanks for your help. It does have isblank(), but I can't get it to work.....below code does the copying down to the last row, but the blanks are changed to zeros. I found this example on the web for the isblank() command =IF(ISBLANK(A2),"",A2*3) , but when I added it to my range statement (after changing the cell addresses), it caused an error on the statement--I didn't write the error down and I don't have time at the moment to go back and try it.......I'll have to take another look tomorrow, unless someone knows how the isblank() command would fit into my formula.....thank you!

Sub ratingsimport()
'
'
Dim LastRowColumnG As Long
Application.DisplayAlerts = False
Range("G2").Select

LastRowColumnG = Cells(Rows.Count, 1).End(xlUp).Row
Range("G2:G" & LastRowColumnG).FormulaR1C1 = "=+RC[-1]/10" 'this copies to the last row but doesn't handle the blanks
Go to the top of the page
 
JonSmith
post Jun 14 2018, 02:26 AM
Post#8



Posts: 3,919
Joined: 19-October 10



Look into using tables and structured references. Copying down formula's down the last row was addressed back in 2003 and its no longer necessary. Just make it a table and add the formula as a calculated column. Excel will do the rest.

So Listobject Tables and Structured References. Those are your keywords.
Go to the top of the page
 
baffled100
post Jun 14 2018, 02:11 PM
Post#9



Posts: 390
Joined: 10-December 12



Thanks for your help, but does your suggestion help with the blanks turning into zeros issue? That's what I still need help with.......thanks again!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd October 2018 - 09:09 AM