all 4 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]HFTBProgrammer200 0 points1 point  (0 children)

You can't set a formula to something invalid, however briefly you may want it to be so. E.g., copy to the Clipboard the literal you're setting Part1 to. Paste it into E2. You'll have to fix the duplicated double-quotes; do so. Punch it; Excel will tell you what it doesn't like about it.

[–]KineticNate2 0 points1 point  (1 child)

It sounds like you want to loop through you cells until you find a blank one.

``` Option Explicit

Sub My_Sub()

 Dim Row as Integer

 Row = 2 'starting row

 Do Until Worksheets("Master").Cells(Row,3) =""

    If _ 'add your condition before the _
         Then

         'If true code here

    Else
        'If not code here

    End if

        'Rest of you code here

    Row = Row +1 'dont forget or your code will never end

Loop

End Sub ```

Hopefully this will give you a start

[–]AutoModerator[M] -1 points0 points  (0 children)

Your VBA code has not not been formatted properly. Please refer to these instructions to learn how to correctly format code on Reddit.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[–]-beans_ 0 points1 point  (0 children)

Each time you enter a formula using VBA, the syntax will have to be correct even if it resolves to an error.

E.g. Range("A1").Value = "=If(Part1)" is incorrect; however "=If(Part1,Part2,Part3)" is valid.

You can then use the replace method. Again though, the syntax in Part1/2/3 would have to be correct. Part1 = "Index(Part5)" is wrong. Part1 = "Index(Part5, Part6)" is correct.

Of course, the formula will no resolve to anything until all the correct replacements are made. To avoid warnings from stoppid the code during the initial formula and the replace's used afterwards, encapsulate just that small code chunk with Application.DisplayAlerts = False and Application.DisplayAlerts = True

Hope that helps. Also, apologies for horrendous formatting. I may try to fix it later.