Problem with code for setting a range

[Pauljj]

Would someone be so kind as to tell me what faux pas I have created in the following code please. It stops on the second to last line with a type mismatch. I am trying to set a range, so that I can copy it over from one workbook to another. Your help will be greatly appreciated

Sub Macro1()

Dim WBO As Workbook 'original workbook
Dim WBN As Workbook 'new workbook
Dim WSO As Worksheet 'original worksheet
Dim WSN As Worksheet 'new worksheet
Dim FinalRowHinter As Range
Dim StartRowHinter As Range


Set WBO = ActiveWorkbook

Set WSO = ActiveSheet

With WSO.Range("J1:J5000")

Set H = .Find("333 Count", LookIn:=xlValues)

End With

With WSO.Range("k1:k5000")

Set i = .Find(333, LookIn:=xlValues)

End With


FinalRowHinter = WSO.Cells(H, 11)
StartRowHinter = WSO.Cells(i, 1)

 

[mrMozambique]

I believe you've set H as a string "333 Count" when it's looking for an integer. Are you trying to look for the row location of the cell that contains "333 Count"?

 

[Pauljj]

Yes thats right, when I added in a 'watch' it seems to correctly find the location of '333 Count' but yes I am trying to locate the row of where '333 count' is

 

[mrMozambique]

Try identifying your location of "333 Count" as a range (R in below example) and then setting H as the row of that range. You'll have to do the same for 333. Something like:

Dim R as Range
Dim H as Long

Set R = Cells.Find("333 Count", LookIn:=xlValues)
H = R.Row

 

[Pauljj]

MrMozambique, that works a treat, thank you very much

 

[mrMozambique]

My pleasure. Thanks for following up to let me know it worked.