Hello folks, Long time no talk. Ho 1

[feipezi]

Hello folks,
Long time no talk. Hope you're all doing well.

I have a workbook with a few tabs. I want to check first to see if the summary column is already there or not. If it is there, then exit sub or run another sub to create that column. The code is like the following:

Sub FindNCO()
Range(Cells(1, 1), Cells(Cells(1, 1).CurrentRegion.Row, Cells(1, 1).CurrentRegion.Columns.Count)).Select
For Each c In Selection
If InStr(c.Value, "Net Charge Off") Then
Exit Sub
Else:
Exit For
GoTo label
End If
Next
label: Call RunAllTabs
End Sub
But what I have is: if the column is there, the same column will be created again because the 'GoTo label' statements.
Any idea how to fix the bug to get what I want?
Thanks in advance.

Sub RunAllTabs()
For Each st In Worksheets
st.Activate
Call NetChargeOff
ActiveSheet.UsedRange.EntireColumn.AutoFit
Next
End Sub

 

[combo]

Your code checks only first cell, if match then exit sub else exit for and execute RunAllTabs.
Probably you need to check all cells in selection. If so, then:

...
For Each c In Selection
    If InStr(c.Value, "Net Charge Off") Then Exit Sub
Next
Call RunAllTabs
...

Instead of selecting cells you may define a range and loop its cells.
Cells(1, 1).CurrentRegion.Row is simply 1.

combo

 

[feipezi]

Thanks combo. It really works.

One more question for you. I tried to add a symbol on the ribbon and assign a macro to it for guys who do not know how to run macros. I did it for one workbook. As I opened another workbook and pressed the symbol on the ribbon, then Excel opened the first workbook I set up the symbol on. It looks like the macro is not portable/transferrable. I guess I need to do it with Add-Ins, right?
Thanks again.

 

[combo]

Referenced macros code full path. So you can choose between:
- a button in workbook,
- using Workbook_Open event to customise ribbon,
- create keyboard shortcut,
- add-in.

combo

 

[feipezi]

Thanks again combo. I thought about 3 options you mentioned except keyboard shortcut
Take care.