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!

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

Open in new window

Euro5Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongCommented:
check the value of lngLastRowD?

>>lngLastRowD = Sheets("Detail").Range("A80000").End(xlUp).Row

it seems that it returned as 1?

hence, make sure column A contains the data before you execute Sub DIM_3()
1
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Try this.....

lngLastRowD = Sheets("Detail").Range("A80000").End(xlUp).Row+1

Open in new window


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?
0
Michael FowlerSolutions ConsultantCommented:
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

lngLastRowD = Sheets("Detail").Range("A" & Rows.Count).End(xlUp).Row+1

Open in new window

1
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@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.
0
Saurabh Singh TeotiaCommented:
Not sure A Column is the right Column to be used to determine the last row of your data because if the A Column is blank it will give 1 as answer..So the way i will do it..in order to find the last row i won't be dependent on any column and i find the last row of data using a different method and then applying formula..so what i mean is something like this...

Sub DIM_3()

    Dim lngLastRowD As Long

    With Sheets("Detail")
        lngLastRowD = Sheets("Detail").Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

        If lngLastRowD > 1 Then
            .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 If

    End With
End Sub

Open in new window


Saurabh...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Euro5Author Commented:
Very nice. thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.