Clever Geek Handbook

How can I simplify this VBA switch statement so as not to repeat so much code?

I am writing an Excel macro that copies information from 1 sheet and pastes it into another. It has to look for a specific line of text to identify the correct column to copy, and I use a switch statement to traverse the various columns. This happens before Z

, so this is a very long macro. I also need to use this for multiple search terms, which makes the macro too big.

Here's a snippet of code:

Select Case True
  Case Range("A1").Value = "SearchTerm1"
    Sheets("ExportSheet").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Template").Select
    Range("L2").Select
    ActiveSheet.Paste
  Case Range("B1").Value = "SearchTerm1"
    Sheets("ExportSheet").Select
    Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Template").Select
    Range("L2").Select
    ActiveSheet.Paste
  Case Range("C1").Value = "SearchTerm1"
    Sheets("ExportSheet").Select
    Range("C2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Template").Select
    Range("L2").Select
    ActiveSheet.Paste
  Case Range("D1").Value = "SearchTerm1"
    Sheets("ExportSheet").Select
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Template").Select
    Range("L2").Select
    ActiveSheet.Paste
  Case Range("E1").Value = "SearchTerm1"
    Sheets("ExportSheet").Select
    Range("E2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Template").Select
    Range("L2").Select
    ActiveSheet.Paste

It goes through 1 by 1 columns to see if it contains a specific search term. If so, it copies everything under it and pastes it into a cell L2

on a separate sheet. This is a very long macro and I am trying to simplify it. Will a For loop work?

+3


source to share


4 answers


  • Your version has been updated if you prefer this method.



With Sheets("ExportSheet")
Select Case True
    Case .[A1].Value = "SearchTerm1"
        .Range("A2:A" & Cells(.Rows.Count, "A").End(xlUp).Row).Copy Sheets("Template").[L2]
    Case .[B1].Value = "SearchTerm1"
        .Range("B2:B" & Cells(.Rows.Count, "B").End(xlUp).Row).Copy Sheets("Template").[L2]
    Case .[C1].Value = "SearchTerm1"
        .Range("C2:C" & Cells(.Rows.Count, "C").End(xlUp).Row).Copy Sheets("Template").[L2]
    ' and so on
End Select
End With

End Sub
  1. The best option imho is the method Find 



Sub test2()
Dim x&, y&
On Error GoTo errorhandler
    With Sheets("ExportSheet")
        y = .Rows(1).Find("SearchTerm1").Column
        x = .Cells(Rows.Count, y).End(xlUp).Row
        .Range(.Cells(2, y), .Cells(x, y)).Copy Sheets("Template").[L2]
    End With
Exit Sub
errorhandler:
    MsgBox "There is no 'SearchTerm1' in 'ExportSheet'!"
End Sub
  1. For each

    looping over a range of cells is also optimal. I think



Sub test3()
Dim Cl As Range
For Each Cl In Sheets("ExportSheet").[A1:E1]
    If Cl.Value = "SearchTerm1" Then
        Sheets("ExportSheet").Range(Cl.Offset(1, 0).Address(0, 0), _
            Cells(Rows.Count, Cl.Column).End(xlUp).Address(0, 0)).Copy _
        Sheets("Template").[L2]
        Exit For
    End If
Next
End Sub
+2


source


As I understand it, you are really looking for the header from which you need to copy the data. If so:



With Sheets("ExportSheet")
    Dim r As Range: Set r = .Range("1:1").Find("SearchTerm1")
    If Not r Is Nothing Then 
        .Range(r.Offset(1, 0), r.Offset(1, 0).End(xlDown)).Copy _
            Sheets("Template").Range("L2")
    End If
End With
+2


source


I don't have data to test, but this might work (replace all the code you posted with this):

Dim X As Long
For X = 0 To 4
    If Range("A1").Offset(0, X).Value = "SearchTerm1" Then
        Sheets("ExportSheet").Range("A2").Offset(0, X).Resize(Sheets("ExportSheet").Range("A2").Offset(0, X).End(xlDown).Row - 2, 1).Copy
        Sheets("Template").Range("L2").PasteSpecial xlPasteAll
        Exit For
    End If
Next
+1


source


Try it. Everything inside the function was the same except for selecting the source cell, so just put it in the function.

Function copy_data(cell)
    Sheets("ExportSheet").Select
    Range(cell).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Template").Select
    Range("L2").Select
    ActiveSheet.Paste
End Function

Select Case True
  Case Range("A1").Value = "SearchTerm1"
    copy_data("A2")
  Case Range("B1").Value = "SearchTerm1"
    copy_data("B2")
  Case Range("C1").Value = "SearchTerm1"
    copy_data("C2")
  Case Range("D1").Value = "SearchTerm1"
    copy_data("D2")
  Case Range("E1").Value = "SearchTerm1"
    copy_data("E2")
End Select
0


source






More articles:


All Articles