Offset to next non-blank cell

[jkicker]

I am trying to set my string variable fasname to equal the value of the next non-blank cell to the right of the cell that is equal to my other string variable fasacct.

Here's what I've tried:

fasname = hws.UsedRange.Find(fasacct).Offset(0, xlToRight).Value

I've tested by replacing .Offset(0, xlToRight).Value with .Address to make sure it's finding the correct starting point and up to there everything is working. But that part with xltoright is giving me object-defined error. what do i put there instead?

 

[Aryatect]

Try below to select the cell next to fasacct (empty cell) in the same row.

fasname = hws.UsedRange.Find(fasacct).End(xlToRight).Offset(0, 1).Address

 

[jkicker]

Try below to select the cell next to fasacct (empty cell) in the same row.

fasname = hws.UsedRange.Find(fasacct).End(xlToRight).Offset(0, 1).Address

i used this instead and it worked beautifully

fasname = hws.UsedRange.Find(fasacct).End(xlToRight).Value

 

[Joe4]

equal the value of the next non-blank cell to the right

You need to be careful here. xlToRight might not do exactly what you think it does, so might not work in all situations for you.
xlToRight does NOT necessarily pick the next non-blank value to the right of whatever cells it is referencing.
If the cell just to the right of the current cell is blank, it WILL do this. However, there are a few situations in which it will not.
It may pick the last cell before the first blank, or the last cell on the sheet (if there are no populated cells to the right of the current cell).

Note that xlToRight is the same as the keyboard shortcut CTRL + Right Arrow. So, to see exactly how it behaves (and why it may not always work for you), try this simple exercise.
1. On a blank sheet, enter a in cells A1, B1, D1, E1, and F1
2. Select cell A1, then hit CTRL + Right Arrow. It should select cell B1, like you want.
3. Select cell B1, then hit CTRL + Right Arrow. It should select cell C1, like you want.
4. Select cell C1, then hit CTRL + Right Arrow. It should select cell D1, like you want.
5. Select cell D1, then hit CTRL + Right Arrow. Note that it selects cell F1, NOT cell E1, which I think is what you want.
6. Select cell F1, then hit CTRL + Right Arrow. Note that it will go to the last column on the sheet (XFD1 in newer versions of Excel).

So those last two examples could be problematic for you, if I am interpretting your original question properly.
See here for an explanation on exactly on xlToRight works: https://docs.microsoft.com/en-us/office/vba/api/excel.range.end