VBA: How to return a cell reference from a variable that looksup a value?

2
votes

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

2
excelvbamatch

2 Answers

1
votes

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.

1
votes

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 just Range("A1").Value = Range("B1").Value
  • avoid using Select/Active statements in your code