VBA formula paste starting in wrong row
The formulas should start pasting in row 2 and copy down, but they are starting in row 1.
I believe that I need a -1 to move them down a row after counting from bottom, but not sure how to put it in.
Can anyone help? Thanks!
I believe that I need a -1 to move them down a row after counting from bottom, but not sure how to put it in.
Can anyone help? Thanks!
Sub DIM_3()
Dim lngLastRowD As Long
With Sheets("Detail")
lngLastRowD = Sheets("Detail").Range("A80000").End(xlUp).Row
.Range("X2:X" & lngLastRowD).Formula = "=IF(IF(OR(AO2=0,ISERROR(ROUNDUP(($AO2*$AP2*$AQ2)/VLOOKUP($BH2,Summary!$A$3:$Y$100,18,FALSE),0))),$X2,ROUNDUP(($AO2*$AP2*$AQ2)/VLOOKUP($BH2,Summary!$A$3:$Y$100,18,FALSE),0))>Y2,IF(OR(AO2=0,ISERROR(ROUNDUP(($AO2*$AP2*$AQ2)/VLOOKUP($BH2,Summary!$A$3:$Y$100,18,FALSE),0))),$X2,ROUNDUP(($AO2*$AP2*$AQ2)/VLOOKUP($BH2,Summary!$A$3:$Y$100,18,FALSE),0)),Y2)"
'.Range("X2:X" & lngLastRowD).Copy
'.Range("X2:X" & lngLastRowD).PasteSpecial xlValues
'Application.CutCopyMode = False
.Range("BE2:BE" & lngLastRowD).Formula = "=IF(IF(OR(AO2=0,ISERROR(ROUNDUP(($AO2*$AP2*$AQ2)/VLOOKUP($BH2,Summary!$A$3:$Y$100,25,FALSE),0))),$X2,ROUNDUP(($AO2*$AP2*$AQ2)/VLOOKUP($BH2,Summary!$A$3:$Y$100,25,FALSE),0))>Y2,IF(OR(AO2=0,ISERROR(ROUNDUP(($AO2*$AP2*$AQ2)/VLOOKUP($BH2,Summary!$A$3:$Y$100,25,FALSE),0))),$X2,ROUNDUP(($AO2*$AP2*$AQ2)/VLOOKUP($BH2,Summary!$A$3:$Y$100,25,FALSE),0)),Y2)"
'.Range("BE2:BE" & lngLastRowD).Copy
'.Range("BE2:BE" & lngLastRowD).PasteSpecial xlValues
'Application.CutCopyMode = False
.Range("BF2:BF" & lngLastRowD).Formula = "=IF(IF(OR(AO2=0,ISERROR(ROUNDUP(($AO2*$AP2*$AQ2)/VLOOKUP($BH2,Summary!$A$3:$AG$100,33,FALSE),0))),$X2,ROUNDUP(($AO2*$AP2*$AQ2)/VLOOKUP($BH2,Summary!$A$3:$AG$100,33,FALSE),0))>Y2,IF(OR(AO2=0,ISERROR(ROUNDUP(($AO2*$AP2*$AQ2)/VLOOKUP($BH2,Summary!$A$3:$AG$100,33,FALSE),0))),$X2,ROUNDUP(($AO2*$AP2*$AQ2)/VLOOKUP($BH2,Summary!$A$3:$AG$100,33,FALSE),0)),Y2)"
'.Range("BF2:BF" & lngLastRowD).Copy
'.Range("BF2:BF" & lngLastRowD).PasteSpecial xlValues
'Application.CutCopyMode = False
End With
End Sub
Try this.....
This line will give you lngLastRowD=2 if the column is empty of has header only else it will return the first empty row in the column.
Does that help?
lngLastRowD = Sheets("Detail").Range("A80000").End(xlUp).Row+1This line will give you lngLastRowD=2 if the column is empty of has header only else it will return the first empty row in the column.
Does that help?
Not For points!
Further to sktneers answer
To allow for all scenarios use the following to get the last used row. This will work with all versions of excel
Further to sktneers answer
To allow for all scenarios use the following to get the last used row. This will work with all versions of excel
lngLastRowD = Sheets("Detail").Range("A" & Rows.Count).End(xlUp).Row+1
@Michael
Actually that is the correct syntax to find the first empty row in a column.
Me bad. I should have pointed it out.
Thanks for making the correction as I believe we should try to eliminate any wrong approach adopted in the code no matter if it is asked or not.
Actually that is the correct syntax to find the first empty row in a column.
Me bad. I should have pointed it out.
Thanks for making the correction as I believe we should try to eliminate any wrong approach adopted in the code no matter if it is asked or not.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Very nice. thank you!
>>lngLastRowD = Sheets("Detail").Range("A8
it seems that it returned as 1?
hence, make sure column A contains the data before you execute Sub DIM_3()