MACRO clearing out formula in Column E.

[Livin404]

Mr. Excel Member Stephen Crump has helped me tremendously. I think the code I do have is somehow erasing the formula I have in Column E. The formula I have is.

=IFERROR(VLOOKUP($D2,'State Country Code'!$A$2:$B$10388,2,0),"").

The code I think may be changing the formula to a value is

Sub Location()
Application.EnableEvents = False
With Range(Range("E2"), Range("E2").End(xlDown))
.Offset(, 1).Value = Evaluate("IF(LEN(" & .Address & ")=2," & .Address & ","""")")
.Value = Evaluate("IF(LEN(" & .Address & ")=3," & .Address & ","""")")
End With
Application.EnableEvents = True
End Sub

I am so sorry for all of this.

 

[Michael M]

So you want the formula to fill E2:E & last row ??....instead of what @StephenCrump has provided ??

 

[StephenCrump]

Here is the relevant link, which will provide some context: Move or offset one space if certain criteria is met.

@Livin404, if you want to have formulae in columns E and F, you could try (using Excel 365):

Sub Location()
    
    Application.EnableEvents = False
    Range("E2:E" & Range("D2").End(xlDown).Row).Formula2 = "=LET(v,VLOOKUP($D2,'State Country Code'!$A$2:$B$10388,2,0),TAKE(HSTACK(v,"""",v),,IF(LEN(v)=2,-2,2)))"
    Application.EnableEvents = True

End Sub

 

[Livin404]

The macro that Mr. Crump provided looks at what is in Column E and if the cell consists of only two characters it moves those two characters over to the next Column F. Otherwise, the data remains in the cell. The formula I need to remain in place because it is a VLOOKUP, so the right data is used based on what is in Column D. I have a user form that I use which continually use to add data to my Database. Because the formula for VLOOKUP is changed to a value, any subsequent data in Column D will no longer be evaluated to see if the characters will or not be moved over to Column F.

Thank you,

 

[Livin404]

Here is the relevant link, which will provide some context: Move or offset one space if certain criteria is met.

@Livin404, if you want to have formulae in columns E and F, you could try (using Excel 365):

Sub Location()
   
    Application.EnableEvents = False
    Range("E2:E" & Range("D2").End(xlDown).Row).Formula2 = "=LET(v,VLOOKUP($D2,'State Country Code'!$A$2:$B$10388,2,0),TAKE(HSTACK(v,"""",v),,IF(LEN(v)=2,-2,2)))"
    Application.EnableEvents = True

End Sub

I have 365.

 

[Livin404]

Here is the relevant link, which will provide some context: Move or offset one space if certain criteria is met.

@Livin404, if you want to have formulae in columns E and F, you could try (using Excel 365):

Sub Location()
   
    Application.EnableEvents = False
    Range("E2:E" & Range("D2").End(xlDown).Row).Formula2 = "=LET(v,VLOOKUP($D2,'State Country Code'!$A$2:$B$10388,2,0),TAKE(HSTACK(v,"""",v),,IF(LEN(v)=2,-2,2)))"
    Application.EnableEvents = True

End Sub

WOW that is amazing, and it solves a lot of my problems. The last thing I'm trying to work around is getting introduced while my "user form" is being activated. On the user form all my activation buttons are on my user form.

Can I put on the worksheet 1 which I named "ACTIVITY"?

I have one other code, on this sheet. I am thinking it maybe the best placed to keep it. I have module and those are macros for clearing, resetting, submit, save, and descend etc. My "call" macro is with the user form. I tried to put it a number of places.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim R As Range, CellRange As Range

    Dim LastRow As Long
    LastRow = Me.Range("A" & Rows.Count).End(xlUp).Row

    Set CellRange = Me.Range("A2:D" & LastRow & ",F2:N" & LastRow)

    If Not Application.Intersect(CellRange, Target) Is Nothing Then
        With CellRange
            .Font.Bold = False
            .Font.Size = 12
            .Font.Name = "Times New Roman"
        End With

        Dim rngArea As Range

        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each rngArea In CellRange.Areas
            rngArea = Evaluate("=Upper(" & rngArea.Address & ")")
        Next rngArea
        Columns.AutoFit
        Application.EnableEvents = True
        Application.ScreenUpdating = True

    End If
End Sub

 

[StephenCrump]

The last thing I'm trying to work around is getting introduced while my "user form" is being activated. On the user form all my activation buttons are on my user form.

Can I put on the worksheet 1 which I named "ACTIVITY"?

I have one other code, on this sheet. I am thinking it maybe the best placed to keep it. I have module and those are macros for clearing, resetting, submit, save, and descend etc. My "call" macro is with the user form. I tried to put it a number of places.

This is a new subject, so I recommend you start another thread, with an appropriate title.

You should also post the relevant code, and be specific about what you're trying to do - your explanation above is quite vague.

 

[Livin404]

This is a new subject, so I recommend you start another thread, with an appropriate title.

You should also post the relevant code, and be specific about what you're trying to do - your explanation above is quite vague.

I got it and I apologize unreservedly. I'll work on it a bit more. Then I'll post if I need to.