Excel - If Statement (VBA) 1

[Adams]

I need to create a VBA code that would

Insert column before column A

Tnen name column "Club"

Insert the club name based on info in column E
if last 8 digits (this is in a string)=
Michigan then assign MI
Nebraska then assign NE

Any suggestions.......I am not very strong in VBA.

Thanks

 

[CassandraR]

Hi:

As for the first part of your question:

    Application.Goto Reference:="R1C1"
    Selection.EntireColumn.Insert
    ActiveCell.FormulaR1C1 = "Club"
    Range("A2").Select

For the second part of your question (state initials), there are two basic routes that I would consider:
1.

Select Case

2. Table lookup.

HTH,
Cassandra

 

[CassandraR]

Hi:

Here's the "Select Case" version that I created for doing the provinces and territories of Canada. I trust you can modify it for the states of USA.

Cassandra

Option Explicit

Sub Clubs()
'
' Clubs Macro
' Macro recorded 11/05/2004 by
'   Cassandra Roads, P. Eng.
'   Professional Logics Corporation
'
'
    Dim strStateFull As String, strStateShort As String, I As Integer

    Application.Goto Reference:="R1C1"
    Selection.EntireColumn.Insert
    ActiveCell.FormulaR1C1 = "Club"
 
    I = 2
    Application.Goto Reference:="R2C5"
    Do While ActiveCell.Value <> ""
        strStateFull = Trim(UCase(ActiveCell.Value))
        Select Case strStateFull
            Case "ALBERTA"
                strStateShort = "AB"
            Case "BRITISH COLUMBIA"
                strStateShort = "BC"
            Case "SASKATCHEWAN"
                strStateShort = "SK"
            Case "MANITOBA"
                strStateShort = "MB"
            Case "ONTARIO"
                strStateShort = "ON"
            Case "QUEBEC"
                strStateShort = "PQ"
            Case "PRINCE EDWARD ISLAND"
                strStateShort = "PEI"
            Case "NEWFOUNDLAND AND LABRADOR"
                strStateShort = "NF"
            Case "NEW BRUNSWICK"
                strStateShort = "NB"
            Case "NOVA SCOTIA"
                strStateShort = "NS"
            Case "YUKON"
                strStateShort = "YT"
            Case "NUNAVUT"
                strStateShort = "NT"
            Case "NORTHWEST TERRITORY"
                strStateShort = "NWT"
        End Select
        Application.Goto Reference:="R" & I & "C1"
        ActiveCell.FormulaR1C1 = strStateShort
        I = I + 1
        Application.Goto Reference:="R" & I & "C5"
    Loop

End Sub

 

[SkipVought]

Hi,

Insert column before column A

Cells(1, "A").entirecolumn.insert Shift:=xlToRight

Tnen name column "Club"

Cells(1, "A").Value = "Club"

Insert the club name based on info in column E
if last 8 digits (this is in a string)=
Michigan then assign MI
Nebraska then assign NE

dim r as range
for each r in range(cells(2, "E"), cells(2, "E").end(xldown))
  with r
    select case right(.value, 8)
      case "Michigan"
        cells(.row, "A").value = "MI"
      case "Nebraska"
        cells(.row, "A").value = "NE"
    end select
  end with
next

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Adams]

Thank you that works great......Now I have one other step that needs to be done.

Sort column A
Based on column A (MI, NE)
copy MI to new Worksheet and name MI
copy NE to new worksheet and name NE

Thank you for your help.

 

[SkipVought]

Turn on your Macro Recorder and sort away!

Rather than COPY, I would use the PivotTable Wizard to REPORT the values that you want on each sheet.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884