Using VBA code to search and minus cell value

[Closed]
Report
-
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
-
Hello!

I'm desperately trying to do a macro button that can lookup a table's column and row headers (from 2 lookup_values entered in 2 cells), then identify the datacell in the table to subtract 1 from the cell value but I can't figure out how to.

This may seem like a really stupid question, but I haven't really learnt VBA so I was wondering if someone could teach me how to code this :'(

Thank you so much!



1 reply

Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
Hi Ly2424,

Have a look a the code below. Let the green text help you understand the code:
Sub RunMe()
Dim fCol, fRow As Range
Dim cHeader, rHeader As String

cHeader = Range("K1").Value 'This cell refers to the column header to lookup
rHeader = Range("K2").Value 'This cell refers to the row header to lookup

Set fCol = Rows(1).Find(cHeader) 'Finding the column header
Set fRow = Columns("A").Find(rHeader) 'Finding the row header

Cells(fRow.Row, fCol.Column).Value = Cells(fRow.Row, fCol.Column).Value - 1 'subtracting 1

End Sub


Best regards,
Trowa