Can Range's target be a cell value or a vlookup?

[Bambiontoast]

I am new to VBA, and tried to find something on google that would teach me how to change a value in my sheet. I found this code:

[COLOR=#252C2F][FONT=Helvetica]Range("A1").Value = 1+Range("A1").Value[/FONT][/COLOR]

Now, this is exactly what I'm looking for - BUT - I need the "A1" to not be a static cell address, but take the cell address from a value found in a cell.

For example, say A1 contains the below formula:

=CELL("address",INDEX(B5:D11,MATCH(G6,B5:B11,0),2))

This formula returns the address of a cell, and my goal is to increase the value of the cell found at this address by 1 by pressing a button.

How do I tell VBA to either:
- Use the value of a specific cell and convert that into a Range object (i.e. above formula returns value 'C10', I therefore want to increase value in cell C10 by 1)
- Perform a Vlookup or Index type function itself to find the correct cell to change, based on lookup value G6

I would prefer the former option since I want to keep as much to formulas as possible, but if there is a far easier way to do all of this in code (i.e. skipping the =Cell("address") function) then that would also be fine. If the best way to achieve this is through code alone, could you please explain the critical parts of the code so I can understand how to change or adapt it as required.

Thanks a lot!




,0),2))

 

[Roderick_E]

dim celladd as string
celladd = range("A1")
[COLOR=#252C2F][FONT=Helvetica]Range(celladd).Value = 1+Range(celladd).Value[/FONT][/COLOR]

 

[wideboydixon]

Range(Range("A1").Value).Value = 1 + Range(Range("A1").Value).Value

WBD

 

[steve the fish]

Im not sure if ive missed something by these replies but i think the op wants to add 1 to an offset of a found cell? Anyways this is relatively simple to understand if that is the case.

Dim c As Range, sh As Worksheet
Set sh = Sheets("Sheet1") 'sheet to search on
With sh.Range("B5:B11") 'range to search in
    Set c = .Find(sh.Range("G6").Value, After:=sh.Range("B11"), LookIn:=xlValues, _
    LookAt:=xlWhole, SearchDirection:=xlNext) 'find our value in G6 within our range (after is the last cell in range so it starts at first cell)
    If Not c Is Nothing Then 'if found
        c.Offset(0, 1).Value = c.Offset(0, 1).Value + 1 'offset one cell to right and add 1
    End If
End With

 

[Bambiontoast]

Thanks for the helpful replies! WBD's code is exactly what I wanted.