Macro to insert "vacant" into a cell based on criteria in another cell

[switchblade13]

First post and very green when in comes to Macros.

I have a spreadsheet with multiple columns. Column 4 is Tenant Name and Column 6 is a Notes field.

If column 6 says "Vacant" I want the Vacant to show in Column 4.

After putting "Vacant" in column 4 I want to delete columns 6,7 and 8.

Any help would be greatly appreciated.

Thanks,
Barry

[lancer102rus]

Do you really want to delete columns? Or you need to clear cells in columns 6,7,8?

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[switchblade13]

Maybe:

Please Login or Register  to view this content.

Thank you very much..with a slight tweek it works perfectly. I had my column numbers off in my initial post.

Sub switchblade13()
Dim rcell As Range
Dim x As Long
x = Range("H" & Rows.Count).End(3).Row
For Each rcell In Range(Cells(2, 8), Cells(x, 8))
If rcell.Value = "VACANT" Then
rcell.Offset(, -3).Value = "VACANT"
Range(Cells(rcell.Row, 8), Cells(rcell.Row, 10)).ClearContents
End If
Next rcell
End Sub

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback. In the future when posting code please use code tags - See Forum Rule No. 3 posted below. If you consider this thread as solved then please mark it as solved - See Forum Rule No. 9 posted below.

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the [#] button at the top of the post window (if you are editing an existing post, press Go Advanced to see the [#] button). The result will appear like this in the post window:

Please Login or Register  to view this content.

... and appear like this when posted:

[Select Code] copy to clipboard

your code here ...
and here ...
and here

You can also type the code tags in manually if you prefer. For more information about these and other tags, click here.


9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.