Macro which assigns then searches for variable only works 1/2 the time?

[jamieboss]

Hi there,

I have a relatively simply macro which is designed to automatically move the cursor to the cell where a new value needs to be entered. The correct row is found by assigning a variable then using excel's search function. The macro works some of the time but frequently goes to the wrong cell/ row. I am hoping someone can tell me how to change the macro so that it always works. The macro works as follows:

1. Moves to a specific cell, then stores that cell entry as a variable
2. Searches for that variable on another sheet to find the correct row
3. Moves the cursor along that row to another column and assign another variable to that value
4. Searches for that variable on another sheet
5. Moves the cursor to the correct cell so that a new value can be entered

Sub PriceChangeEasy()

' Keyboard Shortcut: Ctrl+p
'
Dim VesselName As String
Dim ProductID As String

'moves up a row
ActiveCell.Offset(-1, 0).Range("A1").Select

'moves cursor to far left
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select

'moves cursor back to right so that activecell is cell containing variable of interest and assigns variable name
ActiveCell.Offset(0, 3).Range("A1").Select
VesselName = Selection.Value

'Selects another sheet and searches for variable in that sheet
Sheets("Calc").Select
Range("D:D").Select
Selection.Find(What:=VesselName, After:=ActiveCell, LookIn:= _
xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext _
, MatchCase:=False, SearchFormat:=False).Activate

'moves cursor across to different column on the same observation to related 'key' column
ActiveCell.Offset(0, 5).Range("A1").Select

'assigns variable to the value in the new column
ProductID = Selection.Value

'finds the new variable on a different sheet
Sheets("BS").Select
Range("H:H").Select
Selection.Find(What:=ProductID, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Range("A1").Select

End Sub



Thanks very much for any opinions offered!

Jamie

 

[wigi]

Hello Jamie

I hope I got it all right with all those selections and activate - did you know selecting is not needed in VBA?

Sub PriceChangeEasy()

' Keyboard Shortcut: Ctrl+p

    Dim VesselName As String
    Dim ProductID As String

    VesselName = ActiveCell.Offset(-1).End(xlToLeft).End(xlToLeft).End(xlToLeft).Offset(, 3).Value
    ProductID = Sheets("Calc").Columns("D").Find(What:=VesselName, LookIn:=xlValues, LookAt:=xlPart).Offset(, 5).Value
    Application.Goto Sheets("BS").Columns("H").Find(What:=ProductID).Offset(, 1), True
    
End Sub

I do think my code will encourage you to think more in terms of writing code instead of recording it and then slightly rearranging. If there are still questions, please ask again.

PS: Please use code tags when you paste VBA code on the forum.
To add code tags, you should type in your post:

then paste your code, and lastly, type:

['/code]

WITHOUT the ' in front of the /

Thanks for the consideration.

 

[jamieboss]

Wigi,

Thanks for the reply. Your suggestion is certainly much more eloquent and efficient. I have replaced my code with yours and although the macro works, it does the same thing of finding the correct row some of the time and not other times? I'm out of ideas on why/ how this happens when the variable is assigned right there in the same module?

Jamie

 

[wigi]

In the code, you define the logic of finding the correct spot. If you can't find it always, the logic must be flawed. So you should revise the logic and adapt the code.