Inserting a variable into a formular

Microsoft Microsoft excel 2007 full vers...
May 2, 2010 at 16:22:30
Specs: Windows 7 pro, i7 6GB
Hello, I could use a bit of help on a visual basic macro in Excel. I run the macro from downloaded data, but from day to day, the download could have from 10 to 15 lines in it. I would like to determine how many line are in the sheet, so that I may change the number “11” in this formula to the number of lines in the sheet.
=HLOOKUP("Account Value",$A$1:$S$21,11,FALSE)
As it is now, I must change the “11” to “10” or “9” etc… each day.
I used the COUNTA statement to count the lines and made a variable as below;
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=COUNTA(R[-24]C[-3]:R[-10]C[-3])"

Dim LINES As Integer
LINES = Range("d25").Value
Range("E25").Value = LINES

I thought if I declared a variable, perhaps I could make the formula as below
=HLOOKUP("Account Value",$A$1:$S$21,X,FALSE) -----{note the X}
And then insert the variable where you see the letter X.
I would like to know the format to insert the variable LINES into the above formula.
I would appreciate any ideas.

See More: Inserting a variable into a formular

Report •

May 3, 2010 at 04:47:04
The most common way to determine the last row that contains data in a column is as follows:

Assume we want the last row with data in Column A:

lastRow = Range("A" & Rows.Count).End(xlUp).Row

=HLOOKUP("Account Value",$A$1:$S$21,lastRow,0)

Report •

May 3, 2010 at 07:13:26

Just add your COUNTA() into the HLOOKUP()

=HLOOKUP("Account Value",$A$1:$S$21,COUNTA(A1:A20),FALSE)

This assumes that there is data in column A.
I used A1:A20 as this extends beyond the range of expected data.

If you use Formula auditing, you can step through the formula and see that COUNTA(A1:A20) is converted into the required value , 9, 10 or 11.

I wasn't able to test this, so please try it and if it does not work, describe what happens.


Report •

May 4, 2010 at 05:01:29
Thank you for those two excellent solutions. I also found my own solution as below. It’s not as elegant as the two, but is a good exercise in the use of variables.

ActiveCell.FormulaR1C1 = "=COUNTA(R[-24]C[-3]:R[-10]C[-3])"

Dim NO_LINES As Integer

NO_LINES = Range("d25").Value

Range("D25").Value = NO_LINES

' Put formula for HLOOKUP into QUERY

ActiveCell.FormulaR1C1 = "=HLOOKUP(""X"",R1C1:R20C19," & NO_LINES & ",False)"

Thanks again!

Report •

Related Solutions

May 4, 2010 at 06:17:03
A couple of points....

First, rarely do you have to Select a range to perform an action on it. It is more efficient if you don't Select the Range and it makes the code easier to follow.

Range("D25").FormulaR1C1 = "=COUNTA(R[-24]C[-3]:R[-10]C[-3])"

Second, I'm a little confused as to what you are trying to do.

As far as I can tell you are:

1 - Putting the COUNTA formula in D25
2 - Setting the variable NO_LINES equal to the result of that formula.
3 - Setting D25 equal to the value of NO_LINES, thus replacing the formula with the result of the formula.

If that's the case, why not just use the COUNTA function in VBA and put the final result directly in the cell?

Range("D25") = WorksheetFunction.CountA(Range("A1:A15"))

Instead of putting the formula in the cell, this will put the value (e.g. 11) in the cell.

You could then use that value in your HLOOKUP:

Range("your_range").FormulaR1C1 = "=HLOOKUP(""X"",R1C1:R20C19," & Range("D25") & ",0)"

If you are only using the COUNTA to determine the value for use with HLOOKUP and don't really need it in a cell, you can combine everything into one VBA line:

Range(your_range).FormulaR1C1 = "=HLOOKUP(""X"",R1C1:R20C19," & _
 WorksheetFunction.CountA(Range("A1:A15")) & ",0)"

Finally, if that is all you are doing with the VBA, you can combine the HLOOKUP and COUNTA right in the Excel cell:


Report •

May 4, 2010 at 16:47:41
Thank you I'll try it and let you know how it worked,
One more question, Can I put the contents of a cell in a message box, without making it a variable?
Thanks again.

Report •

May 4, 2010 at 17:38:55
Click on Sheet1!A4 and type this in:


Now run this code:

Sub MsgFromCell()
 MsgBox "The Answer To Your Question Is " & Sheets(1).Range("A4")
End Sub

Report •

May 5, 2010 at 04:50:27
Yes,Yes, and thank you again, my program is complete, and I learned a lot about variables in Excel.

Report •

Ask Question