Title: Minor adjustment to VBA Module to eliminate redudancy

KP_SoCal

Board Regular
Joined
Nov 17, 2009
Messages
116
Instead of this (from code at the bottom of thread):
Code:
FinalTweeks2 targetRange, "GroupA"
FinalTweeks2 targetRange, "GroupB"
FinalTweeks2 targetRange, "GroupC"
I would like to do something like this:
Code:
FinalTweeks2 targetRange, left("Group,5")
Obviously my attempt does not work, but would appreciate any suggestions. Thanks!


Code:
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
 
Last edited:

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,764
Office Version
365
Platform
Windows
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

Board Regular
Joined
Nov 17, 2009
Messages
116
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,271
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
Code:
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,521
Messages
5,511,785
Members
408,864
Latest member
cmajewsk

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top