Find & Rename Text

[DekHog]

Hi all...

Can anyone help get me started with a bit of VBA for a macro I'm trying to put together ? (newbie...very)!

I've got a column (A) with text in it, but some of the references have been truncated by the program it was exported from... it runs like:

SAT-200621
SAT-200621
-200621
SAT-200731

As you can see, the 'SAT has been removed - what I need is a bit of code to insert into the macro to look at column A and add the 'SAT' to anything that doesn't have it.

Just to explain - the prefix 'SAT' is on everything (or should be) and the next 4 figures are the year with the last two being the week number.

My other issue is this... is there any way to rename a worksheet in VBA without knowing the original name? The program outputs names like ~A24521 on the tab, and I want to select the workbook by browsing to it then renaming the sheet to 'Data' using VBA?

Many Thanks

[Bob Phillips]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow
If .Cells(i, "H").Value = "X" Then
If Not Left$(.Cells(i, TEST_COLUMN).Value, 3) = "SAT" Then
.Cells(i, TEST_COLUMN).Value = "SAT" & .Cells(i, TEST_COLUMN).Value
End If
End If
Next i

End With

End Sub


and



Activeworkbook.Worksheets(1).Name = "Data"

[DekHog]

Hmmm, I'm wondering what I can be doing wrong as it's not adding the 'SAT' prefix at all??

It would probably be better if I understood what it was doing! :bug:

I was expecting something simple like an IF statement... how wrong can one be? :)

[Bob Phillips]

Sorry, detritus from previous code



Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow
If Not Left$(.Cells(i, TEST_COLUMN).Value, 3) = "SAT" Then
.Cells(i, TEST_COLUMN).Value = "SAT" & .Cells(i, TEST_COLUMN).Value
End If
Next i

End With

End Sub

[DekHog]

Thanks, xld, that works a treat... :thumb

I'll probably be back for some more VBA hocus pocus, as the sub is far from finished, but I'm trying to go one step at a time and learn what's happening as I go...

[DekHog]

Ok.... I'm getting there... very slowly!!

Help required with the attached worksheet which includes the code above in it.... text box explains what is required.

What's in the sub at present fixes the weird formatting on the first sheet and adds the 'SAT' prefix just fine, so just splitting the totals for E, I & M is the problem now.

TIA to anyone who can help - I'm not being lazy, I am learning from this, honestly! :yes

[Bob Phillips]

Why not just use a formula

=SUMPRODUCT(--(LEFT('~VRP3566'!$G$1:$G$200,1)=TRIM(Sheet1!H$2)))

[DekHog]

Hmm, can't get it to work... see attached workbook after sub has been run and formula pasted in... totals for individual E, I & M are higher than total instances? I don't know where it's getting the numbers from....

[Bob Phillips]

They look right to me.

[DekHog]

Ok, I know where it's going wrong, but wouldn't even try explaining it in here!

Please see attached workbook - text box has details.

Hope you can help...

Many Thanks