calculation by vba

[agog12]

vba is placed in a seperate file c.xlsm
both files are located in a same place
there is a file name sample1.xlsx
open sample1.xlsx
in column N we have to use the formula =H2/M2 and paste the result in values in column N of sample1.xlsx
and in coulmn Q we have to use the formula =N2*P2 and paste the result in values in column Q of sample1.xlsx
and save and close the sample1.xlsx


note we have to use the formula till the end of the data (till the column H has data )
example if column H has data till H17 then we have to use the formula till
N17 & Q17

so plz have a look sir and help me in solving this problem by vba macro sir

 

[DanteAmor]

Try this

Sub calculation()
  Dim wb As Workbook, sh As Worksheet, lr As Long
  Set wb = Workbooks.Open(ThisWorkbook.Path & "\sample1.xlsx")
  Set sh = wb.Sheets(1)
  lr = sh.Range("H" & Rows.Count).End(3).Row
  With sh.Range("N2:N" & lr)
    .Formula = "=H2/M2"
    .Value = .Value
  End With
  With sh.Range("Q2:Q" & lr)
    .Formula = "=N2*P2"
    .Value = .Value
  End With
  wb.Close True
End Sub

 

[snjpverma]

lr = sh.Range("H" & Rows.Count).End(3).Row

@Dante,. What does end( 3) in above line do?

The ”H & rows.count" will return the last row in column H. Then what does "end (3)" do after that?

-VBA learner

 

[DanteAmor]

lr = sh.Range("H" & Rows.Count).End(3).Row
Return the last row with data from column H.

Rows.Count : It is the number of rows of the sheet.

"H" & Rows.Count : Means H1048576

.End(3) or .End(xlUp) : It means moving from H1048576 to up to find a cell with data.

.End(3).row : It means that the row number will return

 

[agog12]

Thnx alot Sir for solving and helping me in solving this problem sir

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[snjpverma]

lr = sh.Range("H" & Rows.Count).End(3).Row
Return the last row with data from column H.

Rows.Count : It is the number of rows of the sheet.

"H" & Rows.Count : Means H1048576

.End(3) or .End(xlUp) : It means moving from H1048576 to up to find a cell with data.

.End(3).row : It means that the row number will return

Thank you for the explanation. So, 3 means the 3rd method of End object, i.e. xlup.

 

[DanteAmor]

Thank you for the explanation. So, 3 means the 3rd method of End object, i.e. xlup.

1 xlToLeft
2 xlToRight
3 xlUp
4 xlDown

 

[agog12]

this code has errors it is pasting in a complete column it has an error plz check the code sir

 

[MARK858]

What does the message box say when you run the code below?

Sub test1()
 Dim wb As Workbook, sh As Worksheet, lr As Long  
Set wb = Workbooks.Open(ThisWorkbook.Path & "\sample1.xlsx")  
Set sh = wb.Sheets(1)
lr = sh.Range("H" & Rows.Count).End(3).Row
MsgBox lr
End Sub