Move cursor based on another cells value

[LostNExcel]

Here is the section of the workbook I having trouble with.

B2 is gross weight of truck and trailer and scrap
B3 is weight of truck only
B4 is weight of box or trailer

if B3 is a zero then I need the active cell to be C3 If greater than zero than B4. When I use the code suggested in this sheet the active cell is always B4 regardless of the value of B3. thank you

[charles]

Hi,


Lets see if this works.

Code: Select all


Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = Range("B2").Address Then
        If Range("B3").Value = "0" Then
            Range("C3").Select
        Else
            Range("B4").Select
        End If
    End If
End Sub


[Sisyphus]

Hello LostNexcel,
I re-wrote the code for you so that you can read it. If you read it you will see which cell is tested, which test is carried out and which cell will be selected by which test result. If you enter test data in a cell that isn't being tested you mustn't be surprised if the test result doesn't meet with your expectations. You now have the capability to either enter test data in the cells specified in the code or modify the code to test the cells that have your data.
I wonder now whether you have more than one set of data on your worksheet. If you have many rows the code provided can't adapt. To write code that can we would need accurate knowledge of your workseet's construction.

[LostNExcel]

Charles thank you very much that works great. I appreciate your time and patience with me on this. Again Thank you

Sisyphus, yes I understand that and made the changes to your code Since we are only discussing these cells here I was able to make the transition. The results were still the same. I will again try and explain I understand what your are saying but for my sake I just broke this down into the simpliest form with just the cells I needed. Once that was working sticking it back into the master workbook was simple. Once back into the master if it didn't work than that's a different issue, but once the simple part worked, and placed in the master book, it worked there also. Maybe just different ways of approaching the problem but I tend to break it down into the smallest component and get that working first. Different strokes I guess. thanks

[Sisyphus]

Hello lostNexcel,
Your method is absolutely commendable! I work the same way.
However, I also suggest that you look at how Charles solved your problem. His code, apparently successful tests the range B3 if it contains "0".
This means two things.
Firstly, the cell isn't blank as you earlier suggested.
Secondly, that it contains text rather than a number.
Look at this particular line in earlier suggestions. The test failed because the wrong thing was tested. Had you not posted the workbook we might not have guessed the format of the cell, instead relying on what you told us about it.

I am explaining this to you because it is rather odd to enter numbers in Text format, and the now successful code will fail if a text zero isn't entered. So, if you decide to handle the test cell in a more conventional manner in future you should be able to get the code to work again by amending the test here discussed.

Convention wouldn't have numbers entered as text and would prefer nothing over entering zero. If no zero entry is required the cell can be recognised as blank regardless of its format. On the other hand, if it is formatted as number a test would see it as containing a zero value regardless of whether it is blank or holds zero.

[LostNExcel]

Not sure about the why you are saying. All I can say is that I enter a zero as a zero not as text. to confirm that I did format the cell as numeric both results are the same. I'll stand by my orginal description the cell had a zero in it entered as a zero not as text. thank you but I think this will end this discussion.