[Macro] Lowest row value in a formula

guston · May 24, 2011

Dear all..

I have this simple problem with my macro

There are two sheets, "New Asset" for data input, and "Asset list" for output. I already create macro function to insert all data from the new asset to asset list, in a way that it copy and paste the data to the lowest row.
I have to update the reference of cell C2 (in New Asset), to the lowest row in column F (in Asset List) after executing the macro.
The only problem is with this macro formula: 

Range("C2").Formula= "='Asset List'!F3"

I wanted to change the "F3" in the macro so that it dynamically refer to the lowest row in the F column in the "asset list" sheet.

The whole macro code is:


Sheets("Asset List").Select

Range("B1:P1").Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select

ActiveSheet.Paste
ActiveCell.Offset(-1, 0).Select

Selection.PasteSpecial Paste:=xlPasteValues</pre>

Sheets("New Asset").Select
Range("B2:C13").Select
Selection.ClearContents

Range("C2").Formula= "='Asset List'!F3"

It probably not the best/efficient code, if there are any suggestion about the whole code is also welcomed

. Note: there are merged row for row B1-P1 at asset list, thats why i managed to copy paste all the row. And for every data, i start from column B, not A.

I would like to thank you guys for all the help.

Peter_SSs · May 24, 2011

Welcome to the MrExcel board!

Are the values in column F of 'Asset List' numerical, text, or could be either?

If always numerical, consider making the C2 formula something like this. Where I have 1000, just make sure this is greater than the number of rows you are ever likely to have.

=LOOKUP(9.99E+307,'Asset List'!F1:F1000)

If always text

=LOOKUP(REPT("z",255),'Asset List'!F1:F1000)

If it could be either

=LOOKUP(2,1/(1-ISBLANK('Asset List'!F1:F1000)),'Asset List'!F1:F1000)

By doing this, you shouldn't need to bother with changing the formula in your macro.

guston · May 24, 2011

Dear Sir..
The value in column F (Asset List) is a formula which generate numeric which i combine with text data.

eg: F3=(A5+B5)&"excel"

it means, the macro code for:

Range("C2").Formula= "='Asset List'!F3"

Become :

Range("C2").Formula="='Asset List'!(LOOKUP(2,1/(1-ISBLANK('Asset List'!F1:F1000)),'Asset List'!F1:F1000)"??

I really wanted to just change the reference in the
"Range("C2").Formula= "='Asset List'!F3", and prefer not to change anything else, because i dont quite understand the whole macro code. In other word, the formula need to be placed in the macro code.

Peter_SSs · May 24, 2011

guston said:
Dear Sir..
The value in column F (Asset List) is a formula which generate numeric which i combine with text data.

eg: F3=(A5+B5)&"excel"

it means, the macro code for:

Range("C2").Formula= "='Asset List'!F3"

Become :

Range("C2").Formula="='Asset List'!(LOOKUP(2,1/(1-ISBLANK('Asset List'!F1:F1000)),'Asset List'!F1:F1000)"??

I really wanted to just change the reference in the
"Range("C2").Formula= "='Asset List'!F3", and prefer not to change anything else, because i dont quite understand the whole macro code. In other word, the formula need to be placed in the macro code.

If your formulas in column F of 'Asset List' are ...
=(A5+B5)&"excel" (presumably copied down)
...then the values in that column are text.

My suggestion for that circumstance was to put the formula

=LOOKUP(REPT("z",255),'Asset List'!F1:F1000)

directly into cell C2 of the 'New Asset' sheet, not in your code.

This formula will always return the last text value in column F (within the rows mentioned in the formula which are 1 - 1000 in my example) no matter what row it is on. Therefore you wouldn't even need the sectuion in your macro code to adjust this formula each time you copy new data into the 'Asset List' sheet.

You did mention that you don't really understand macro code that well, so this method should help since it eliminates some of the code. All your code needs to do is copy the new data in.

guston · May 24, 2011

Thanks for all your help Peter. The formula for cell C2 work great

I manage to alter the code a bit so that cell C2 is not selected when the macro reset all the data, and use your formula for cell C2.

I was just wondering, if the whole macro code i am using is efficient enough, because after i put all the data in, the process is going a bit slow. Probably it is because the huge database, the macro code, or the formulas. The heaviest formula i use is "sumproduct" which i use in 1 column only, and the database is around 1500 row. While the final coding is:

Sub InputData()
Sheets("Asset List").Select

Range("B1:P1").Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select

ActiveSheet.Paste
ActiveCell.Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues

Sheets("New Asset").Select
Range("C3,C4:D14").Select
Selection.ClearContents

End Sub

I really need some opinion on this, so that i can focus on which section i need to work on first. Again, thank you for all the help

Peter_SSs · May 26, 2011

guston said:
I was just wondering, if the whole macro code i am using is efficient enough, ...

You rarely need to actually 'select' anything to work with it in vba, and selecting slows your code considerably. Having said that, I don't think the last code you posted should be much of a problem since it has only a few steps. From what I can understand of the code, I think it could be replaced with this and it would be marginally quicker.

 Sub InputData2() With Sheets("Asset List") With .Range(.Range("B1").End(xlDown), .Range("B1").End(xlDown).End(xlToRight)) .Copy Destination:=.Offset(1) .Value = .Value End With End With Sheets("New Asset").Range("C3,C4:D14").ClearContents End Sub

guston said:
... after i put all the data in, the process is going a bit slow. ... The heaviest formula i use is "sumproduct" which i use in 1 column only, and the database is around 1500 row.

I suspect this is more likely to be your problem. SUMPRODUCT can be fairly resource-hungry.

JD_GFI · May 26, 2011

I try to avoid using sumproduct for that exact reason. I had a csv import of 10k+ rows and used sumproduct to match between 2 dates. It took over 30 minutes and maxed out cpu usage on 4 cores to update.

Changed all the formulaes to use a combination of pivot tables and GETPIVOTDATA and the workbook totally refreshes in under 15 seconds!

[Macro] Lowest row value in a formula

guston

New Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

guston

New Member

Peter_SSs

MrExcel MVP, Moderator

guston

New Member

Peter_SSs

MrExcel MVP, Moderator

JD_GFI

Board Regular

Similar threads

Forum statistics

Share this page

[Macro] Lowest row value in a formula

guston

New Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

guston

New Member

Peter_SSs

MrExcel MVP, Moderator

guston

New Member

Peter_SSs

MrExcel MVP, Moderator

JD_GFI

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock