How to specify a range in a formula using a variable

[ThrottleWorks]

Hi,

I am trying below formula in excel.

“=VARP(b1:b1500)”


I want to use a variable & replace b1500.

I need to use last row count value instead of b1500.

How do I write this formula in VBA by using Cells(1,1) fashion.


Can anyone help me in this please.

 

[NARAYANK991]

Hi Sachin ,


If I assume that you have already defined and calculated a variable called LastRow , then the following VBA statement will work :


Application.WorksheetFunction.Varp( Range("B1").Resize(LastRow))


where LastRow will have a numeric value , say 1500.


Within an Excel worksheet formula , the following should work :


=VARP(OFFSET(B1,0,0,LastRow,1))


Narayan

 

[kaushik03]

Hi Sachin,


See the below code:


Sub LstRowCnt()


'To identify the last row in column A at sheet1


lrow = ThisWorkbook.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row


'Now you can pass the 'lrow' variable to range or cell object. For e.g., say you want to select last row (filled with data) of column A at sheet1.


Sheets("sheet1").Cells(lrow, 1).Select


End Sub


PS: Put some data at the column and sheet of your choice (and ajust the reference in code) to test the code.


Hope this helps.


Regards,

Kaushik

 

[kaushik03]

Sachin,


Sorry...I just missed the varp part of your post and got it in a different way....Narayan has replied it correctly...

 

[ThrottleWorks]

Hi Narayan, thanks for the help. However I am facing a problem.


the code is running but no value is populated, cell remains blank.


I am using following codes.


lastrow = RawData.Range("a" & Rows.Count).End(xlUp).Row


RawData.Range("b1").Select

Application.WorksheetFunction.VarP (Range("b1").Resize(lastrow))


Would request you to help me

 

[ThrottleWorks]

Please note, if i try this formula in imme window (Ctrl G) I am getting the result.

But I am not getting the result in worksheet.


?Application.WorksheetFunction.VarP (Range("b1").Resize(lastrow))

1.22706532732028E-04

 

[NARAYANK991]

Hi Sachin ,


This should work :

[pre]

Public Sub temp()
Dim RawData As Worksheet
Set RawData = ThisWorkbook.Worksheets("Sheet Name")

lastrow = RawData.Range("a" & Rows.Count).End(xlUp).Row

Debug.Print Application.WorksheetFunction.VarP(Range("B1").Resize(lastrow))
End Sub

[/pre]
Narayan

 

[ThrottleWorks]

Sir, this is giving me Run Time Error 9


Subscript out of range


My internal sheet name (name which can be seen in the VBA project without bracket) is RawData.


Name with the bracket is sheet2.


I can see this Sheet2 name in the excel file (not in the VBA).

I have not renamed the sheet2 in excel file.


Please help.

 

[ThrottleWorks]

Sir, I think, I am able to do it now.


Would request you to please tell me if this is correct if you have time.


I have used following code.


Range("ab4").Value = Application.WorksheetFunction.VarP(MyRng)


MyRng is


Dim MyRng As Range


Set MyRng = Range(Cells(4, 9), Cells(lr + 3, 9))

 

[NARAYANK991]

Hi Sachin ,


If RawData is the name of your worksheet , and you are having this code in the Sheet section of your VBA Project , then the following is sufficient :

[pre]

Public Sub temp()
lastrow = Range("a" & Rows.Count).End(xlUp).Row
Debug.Print Application.WorksheetFunction.VarP(Range("B1").Resize(lastrow))
End Sub

[/pre]
For the code which you have posted to work , your data should be present in column I , from I4 till lr+3 , where lr is the last row of data in column A.


Narayan

 

[ThrottleWorks]

Hi Narayan Sir, good morning, wish you & everboydy on the forum a very happy Vijaya Dashami.


Thanks a lot for helping me, have a nice day.