VBA: How to return a cell reference from a variable that looksup a value?
Solution 1
Another option is to use Find function:
Sub Teste2()
Dim res As Range
Set res = Range("A:A").Find(What:=Range("Today"), Lookat:=xlWhole, MatchCase:=False)
If Not res Is Nothing Then
' res.Offset(, 1) will give you column B (1 to the right from A)
res.Offset(, 1).Value = Range("G5").Value
End If
End Sub
Some notes:
- don't use variables with specific names such
Value
. One time you'd be confused with unexpected results - instead using
Copy/PasteSpecial xlPasteValues
you could use justRange("A1").Value = Range("B1").Value
- avoid using Select/Active statements in your code
Solution 2
You are very close with your try just make a few small changes as below and it will work, No need to use a find as it will be slower:
Dim MatchFormula As Long
Dim IndexFormula As Range
MatchFormula = WorksheetFunction.Match(Range("Today"), Range("A:A"), 0)
Set IndexFormula = WorksheetFunction.Index(Range("B:B"), MatchFormula, 0)
IndexFormula.Value = Range("G5").Value
More so if you want it to be even faster you can use:
Dim MatchFormula As Long
MatchFormula = WorksheetFunction.Match(Range("Today"), Range("A:A"), 0)
Range("B" & MatchFormula).Value = Range("G5").Value
This works because the Match on the Column returns the row with your value and you can just reference that row in column B to void the need of using index.
user3276971
Updated on July 09, 2022Comments
-
user3276971 almost 2 years
First of all, I have very little experience with VBA. Here is my problem:
I need to update a certain rate on a daily base in order to perform investments calculations. Right now, I have a button that once clicked brings the day's rate automatically from a website, and I have to copy and paste the rate to its respective date manually. What I would like to do it to just click the button and the rate would automatically be pasted next to its date. The worksheet looks like this:
Update Button Day's date Day's rate 03/01/2013 6% Date Rate 02/01/2013 5% 03/01/2013 6% 04/01/2013
The most obvious way to do it would be to use vlookup to look for the the day's date up right and paste it in the correct place at column B, but the problem would be that everytime I update the value, the previous day formula would result in an error. So the best solution would be to use a simple macro that matches the day's date in column A and pastes only the value of the rate next to it. A good strategy to perform this action with normal worksheet functions would be
=CELL("address";INDEX(B:B;MATCH($G$5;A:A;0);))
, which would result in the cell reference of the right day's rate in column B. The problem is there isn't the =CELL function in VBA.I tried the following script but it didn't work out:
Sub Teste2() Dim IndexFormula As Range MatchFormula = WorksheetFunction.Match(Range("Today"), Range("A:A"), 0) IndexFormula = WorksheetFunction.Index(Range("B:B"), MatchFormula, 0) Value = IndexFormula.Address Range("G5").Select Selection.Copy Value.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub
What code should I use?
Thanks in advance