Set first blanl cell as range

[mattress58]

I need to adjust the code below. I need to set the first blank cell in column E as a perminent spot for later code. If E29 is the first blank cell I want FB = Range("E29") and not the first blank cell. I only want to use Range("E500").End(xlUp) to just find and set the cell in the beginning and then it never changes as data fills in column E.

Sub Try
Dim FB as range
set FB = Range("E500").End(xlUp)

 

[ChrisBM]

Dim FB as Range

FB = Range("E" & Range("E500").End(xlUp).Row)

 

[Rick Rothstein]

I need to adjust the code below. I need to set the first blank cell in column E as a perminent spot for later code. If E29 is the first blank cell I want FB = Range("E29") and not the first blank cell. I only want to use Range("E500").End(xlUp) to just find and set the cell in the beginning and then it never changes as data fills in column E.

Sub Try
Dim FB as range
set FB = Range("E500").End(xlUp).Offset(1)

Try adding the part in red.

 

[mattress58]

set FB = Range("E500").End(xlUp)[B][COLOR=#ff0000].Offset(1)[/COLOR][/B]

Gets me the first blank cell which is perfect, but how do I set FB to that cell from here on out. As data gets intered into column E, I want FB to equal E29 in the example.

Example
E29 is the first blank cell in column e

I do

Dim FB as range
set FB = Range("E500").End(xlUp)[B][COLOR=#ff0000].Offset(1)[/COLOR][/B]

then my macro fills in data into E36

I want FB to still equal E29

I just want to use

Range("E500").End(xlUp)[B][COLOR=#ff0000].Offset(1)[/COLOR][/B]

to set FB to that cell and then it always equals that cell even as the macro goes on and things get filled into column E.

 

[Rick Rothstein]

set FB = Range("E500").End(xlUp)[B][COLOR=#ff0000].Offset(1)[/COLOR][/B]

Gets me the first blank cell which is perfect, but how do I set FB to that cell from here on out. As data gets intered into column E, I want FB to equal E29.

Do you want that functionality only while the workbook is open and every resets the next time the workbook is opened? Or do you want to run the macro for the first time and then have that same cell memorized for the rest of time?

 

[mattress58]

I want it to set it each time the macro is ran. It sets it at the begnning and then keeps it unitl end sub.

 

[Rick Rothstein]

I want it to set it each time the macro is ran. It sets it at the begnning and then keeps it unitl end sub.

Sorry, but I am confused... if you only run the code snippet at the beginning of the sub, and don't execute it again anywhere else in the code, then FB would remain set to that cell until the macro ends.

 

[mattress58]

I will be referencing the spot in the rest of the code.

such as FB.offset(1,0) to get values as they are filled in.

 

[Rick Rothstein]

I will be referencing the spot in the rest of the code.

such as FB.offset(1,0) to get values as they are filled in.

That does not end my confusion. Let me repeat (I think this should be clear)... if you only run the code snippet, namely this...

set FB = Range("E500").End(xlUp)[COLOR=#000000].Offset(1)[/COLOR][COLOR=#B22222][/COLOR][COLOR=#B22222][/COLOR][COLOR=#B22222][/COLOR]

at the beginning of the sub, and don't execute it again anywhere else in the code, then FB would remain set to that cell until the macro ends. You can offset from it all you like as the cell FB is referencing won't change.

 

[mattress58]

AWWWWW..... Sorry, you are correct... I had an error on another part. I thought everytime I put FB it was like putting

Range("E500").End(xlUp)[COLOR=#000000].Offset(1)[/COLOR]

Which would look again at the first blank cell.
I tried it after you first put that but I have the cell reference wrong.
Thank you for the help. This really helps me with what I am doing.