Simple VBA IF THEN to fill a cell with a value

NYEXCEL1

Board Regular
Joined
Apr 17, 2013
Messages
123
I dont have much experience with VBA but know this would be the best way to populate a long document.

If I have a value in column B beginning with cell B2 put the value "2019" in cell J2

Thats it!!



Additionally, i currently use a formula to remove all the data in a cell after a particular character ("!"), using the FIND and LEFT functions, can you add the same formula to VBA and replace the cell with the updated value?

thanks all
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
How about
Code:
Sub Nyexcel1()
   With Range("J2", Range("B" & Rows.Count).End(xlUp).Offset(, 8))
      .Value = Evaluate("if(" & .Offset(, -8).Address & "<>"""",2019,"""")")
   End With
End Sub
 
Upvote 0
Unfortunately i see that blank cells in Column B have a space in them which is causing the the 2019 to be populated in every cell in column J. Is there a way to exclude those cells with this blank space criteria? I upload this template so i would rather not just use TRIM to get rid of these as I do not know what effect it will have


thanks!
 
Upvote 0
Just add a space as shown
Code:
Sub Nyexcel1()
   With Range("J2", Range("B" & Rows.Count).End(xlUp).Offset(, 8))
      .Value = Evaluate("if(" & .Offset(, -8).Address & "<>[COLOR=#ff0000]"" ""[/COLOR],2019,"""")")
   End With
End Sub
 
Upvote 0
Thanks for the quick reply. Still not working and it populates cells that have a blank. could you walk me through the VBA ?
 
Upvote 0
The best thing would be to make the blanks totally empty, otherwise you will probably get problems further down the line.
If you do not want to do that we need to find out what is in those "blank" cells.
Point these formulae at one of the "blanks"
=isblank(a1)
=len(a1)
=code(a1)
what do they return?
 
Upvote 0
For the blank cell i get the following formula answers in your order: TRUE, 0, #VALUE
For the cell i referenced with the space: FALSE, 1, 32
 
Upvote 0
Ok, how about
Code:
Sub Nyexcel1()
   With Range("J2", Range("B" & Rows.Count).End(xlUp).Offset(, 8))
      .Value = Evaluate(Replace("if((@ ="" "")+(@=""""),"""",2019)", "@", .Offset(, -8).Address))
   End With
End Sub
 
Upvote 0
thank you that worked. could you explain the change, specifically the "@" symbol . what does that do?
 
Upvote 0
You're welcome & thanks for the feedback.
The @ is just a "place holder" & is replaced by .Offset(, -8).Address courtesy of the Replace function.
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top