Would you consider my code here to be "inefficient"

[storemannequin]

The reason I ask is because almost everything that I do in VBA involves looping ad infitium...basically. Would you go about this any other way? I can usually always think of a way to do some sort of manipulation, but it always involves looping!? Is that just because I'm a beginner programmer or is there really no better way of going about certain tasks? Appreciate it greatly!

Sub SwapAndExtend()
 
    FC = Cells(2, Columns.Count).End(xlToLeft).Column
    FR = Cells(Rows.Count, 1).End(xlUp).Row
    RowCount = FR - 2
 
    For i = 1 To FC
        If Cells(2, i) = "ASP" Then
        Columns(i).Cut
        Columns(i).Offset(, -1).Insert Shift:=xlToRight
        End If
     Next i
 
 
    For e = 1 To FC
        If Cells(2, e) = "EXT" Then
            Cells(3, e).Resize(RowCount, 1).FormulaR1C1 = "=RC[-2]*RC[-1]"
        End If
    Next e
 
 
  For j = 1 To FC
 
    Select Case Cells(2, j)
 
        Case "QTY"
            Cells(2, j).Resize(RowCount, 1).NumberFormat = "* #,##0"
        Case "ASP"
            Cells(2, j).Resize(RowCount, 1).NumberFormat = "$* #,##0.00"
        Case "EXT"
            Cells(2, j).Resize(RowCount, 1).NumberFormat = "$* #,##0.00"
    End Select
 
  Next j
 
 
End Sub

 

[dave3009]

You could combine the three loops into one and put the other Case options within the Select

Sub SwapAndExtend()
 
    FC = Cells(2, Columns.Count).End(xlToLeft).Column
    FR = Cells(Rows.Count, 1).End(xlUp).Row
    RowCount = FR - 2
 
  For j = 1 To FC
 
    Select Case Cells(2, j)
        
        Case "EXT"
            Cells(3, j).Resize(RowCount, 1).FormulaR1C1 = "=RC[-2]*RC[-1]"
            Cells(2, j).Resize(RowCount, 1).NumberFormat = "$* #,##0.00"
        Case "ASP"
            Columns(j).Cut
            Columns(j).Offset(, -1).Insert Shift:=xlToRight
            Cells(2, j).Resize(RowCount, 1).NumberFormat = "$* #,##0.00"
        Case "QTY"
            Cells(2, j).Resize(RowCount, 1).NumberFormat = "* #,##0"
                 
    End Select
 
  Next j
 
 
End Sub

 

[iliace]

First, do it all in one loop. Explicitly declare variable, turn off calculations and screen updating, and use StrComp instead of = to check for string. Also, If/Then/Else is usually more efficient than Select Case.

Sub SwapAndExtend()
  Dim FC As Long, RowCount As Long
  Dim calcs As Excel.XlCalculation
  Dim j As Long

  Application.ScreenUpdating = False
  calcs = Application.Calculation
  Application.Calculation = xlCalculationManual

  FC = Cells(2, Columns.Count).End(xlToLeft).Column
  RowCount = Cells(Rows.Count, 1).End(xlUp).Row - 2

  For j = 1 To FC
    If StrComp(CStr(Cells(2, j)), "ASP") = 0 Then
      Columns(j).Cut
      Columns(j).Offset(, -1).Insert Shift:=xlToRight
      Cells(2, j).Resize(RowCount, 1).NumberFormat = "$* #,##0.00"
    ElseIf StrComp(CStr(Cells(2, j)), "EXT") = 0 Then
      Cells(3, j).Resize(RowCount, 1).FormulaR1C1 = "=RC[-2]*RC[-1]"
      Cells(2, j).Resize(RowCount, 1).NumberFormat = "$* #,##0.00"
    ElseIf StrComp(CStr(Cells(2, j)), "QTY") = 0 Then
      Cells(2, j).Resize(RowCount, 1).NumberFormat = "$* #,##0.00"
    End If
  Next j
  
  Application.ScreenUpdating = True
  Application.Calculation = calcs
End Sub