Need macro for OFFSET whcih derives ROWOFFSET from a variable in another cell

[MannStewart]

Hi


I have a worksheet with a working range of B5:B5000, G2:G5000, H2, V5:V5000.

where,

V5:V5000 is just =ROW($B5)...downwards,

$G$2 contains a function formula that returns a daily overall air-pollution level in decimal value that I make it recalculate each time the worksheet is Activated,

$H$2 contains a function which returns a whole number that I wrote to indicate the ROW number within the range (G5:G5000) if a category that matches the air-pollution value is detected in column B, and $H$2 is also recalculated ea time the worksheet is Activated,

(G3:G4 are blanks)

G5:G5000 are to be input by me with pollution values that I have been having to key in manually at different cell locations within the column G when the pollution reading matches any of the category code in B column,
(data is now currently populated somewhere about 30% full, scattered within the range),

B5:B5000 are fully populated with prefixed categories codes for different G values,


I am having a hard time with my usual manual input, so I am trying to write a macro to use with my button to COPY the auto recalculated value in G2, everytime I click on it, & PASTEVALUE that value to the cell by using OFFSET into the cell in the G range with the number stated in H2.

I have tried to use the following, but the OFFSET would not bulge or move at all, let alone PASTEVALUE

Can anyone guide me to the correct code?
Here's my existing code that won't work:


Sub PasteVal_AQI()


Range("$G$2").Copy
Range("$G$2").Select
Selection.Offset(H2, 0).PasteSpecial Paste:=xlPasteValues

Range("A1").Select
Application.CutCopyMode = False


End Sub


where,

H2: {=LOOKUP(2,1/(B5:B5000=F2),ROW(B5:B5000)-ROW(B5)+3)}
F2: contains the criteria provided by the organizer that MATCHES the range in B5:B5000



Any help for the right solution will be greatly appreciated..


Stewart.

 

[jasonb75]

You need to specify that H2 is a range for it to work, otherwise vba will think that it is an undeclared variable.

Offset([B]Range("H2").Value[/B], 0)

or cutting out the selection (whch you don't need to use, unless you want your code to run slower)

Sub PasteVal_AQI()
With Range("G2")
    .Copy
    .Offset(Range("H2").Value, 0).PasteSpecial Paste:=xlValues
End With
Application.CutCopyMode = False
End Sub

 

[MannStewart]

Your reply works Great! Can't say enough Thanks JASONB75 !