Title: Minor adjustment to VBA Module to eliminate redudancy

[KP_SoCal]

Instead of this (from code at the bottom of thread):

FinalTweeks2 targetRange, "GroupA"
FinalTweeks2 targetRange, "GroupB"
FinalTweeks2 targetRange, "GroupC"

I would like to do something like this:

FinalTweeks2 targetRange, left("Group,5")

Obviously my attempt does not work, but would appreciate any suggestions. Thanks!

Sub FinalTweeks1()

Dim targetRange As Range
' change this to where you want to work on
Set targetRange = ActiveSheet.Range("A:A")
FinalTweeks2 targetRange, "CategoryA"
FinalTweeks2 targetRange, "GroupA"
FinalTweeks2 targetRange, "GroupB"
FinalTweeks2 targetRange, "GroupC"
End Sub

Sub FinalTweeks2(targetRange As Range, what As String)

Dim found As Range, first As Range
Set first = targetRange.Find(what, After:=Range("A" & Rows.Count), LookIn:=xlValues, LookAt:=xlWhole)
If Not first Is Nothing Then
first.Resize(2, 1).EntireRow.Insert 'inserts two rows above "first"

    Set found = targetRange.FindNext(first)
    Do While (Not found Is Nothing)
        If (found.Address = first.Address) Then Exit Do
         'found.Clear  '''''clears the cell found
         'found.EntireRow.Clear
         'found.EntireColumn.Clear
         found.Resize(, 20).Clear  '''''changes the size of the range A:E
         
        Set found = targetRange.FindNext(found)
    Loop
End If
End Sub

 

[Norie]

How doesn't it work?

Just with a quick glance it's not obvious, apart from left("Group,5").

If you want to get the 1st 5 characters try Left("Group,5",5).

If you want to do something else post an explanation of what it is.

 

[KP_SoCal]

Sorry, I didn't do a very good job explaining this. Basically, I'm passing 5 different variables through FinalTweeks2. When the string of variables starts with "Group", I'm trying to avoid all the additional lines of code by having to key in:

FinalTweeks2 targetRange, "GroupA"
FinalTweeks2 targetRange, "GroupB"
FinalTweeks2 targetRange, "GroupC"

Instead of just something like, FinalTweeks2 targetRange, like "Group%"

Does this make more sense?

 

[AlphaFrog]

That makes sense, but do you want to run FinalTweeks2 for all Groups e.g. "Group*" or do you want to Run it for 5 specific groups Groups A-E and there could be additional Groups besides the specific 5?

The reason I ask is that you could change FinalTweeks2 to run on all groups or you could send it the names of 5 groups?

This is one way you could loop FinalTweeks2 five times for five specific groups

Sub FinalTweeks1()

    Dim targetRange As Range, grp As Variant
    ' change this to where you want to work on
    Set targetRange = ActiveSheet.Range("A:A")
    For Each grp In Array("GroupA", "GroupB", "GroupC", "GroupD", "GroupE")
        FinalTweeks2 targetRange, CStr(grp)
    Next grp
End Sub

 

[KP_SoCal]

An array will work nicely. Thanks! Cheers!!!