Macro not applying to all sheets

S

spyldbrat

Board Regular
Joined
May 5, 2002
Messages
211
Office Version
  1. 365
Hi. I have a macro that selects multiple sheets and is supposed to be applying formula's to all sheets. However, the formula's are only being applied to half the sheets and I don't know why. I am not getting errors when it's selecting the sheets and I can see that all sheets are selected. Any idea why this is happening?

Code: 
Sheets(Array("NY Reg", "NY Lic & Reg", "Skip in Ramco", "Annual", "90 Day", _
        "Support", "NY Term", "PA Stmt #", "PA Print Card Corp", "PA Print Card State", _
        "Print Card - Mark Complete", "PA Emp Ver", "PA Child Abuse", "NJN", "NJS", _
        "Cherry Hill")).Select
    Sheets("NY Reg").Activate
    ActiveWindow.SmallScroll Down:=-15
    Range("F8").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-5],EEMstr,2,FALSE),""Not Listed"")"
    Range("G8").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[5],Dataset,7,FALSE),""No"")"
    Range("K8").Select
    ActiveCell.FormulaR1C1 = _
        "=CONCAT(RC[-3],"","",""; "",R7C[-2],"":"","" "",RC[-2],"","",R[-1]C10)"
    Range("K8").Select
    ActiveCell.FormulaR1C1 = _
        "=CONCAT(RC[-3],"","",""; "",R7C[-2],"":"","" "",RC[-2],"","",R7C10,"":"","" "",RC[-1])"
    Range("L8").Select
    ActiveCell.FormulaR1C1 = "=CONCAT(RC[-11],"" "",RC[-9])"
    Range("M8").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(DATEVALUE(RC[-9]),""Null"")"
    Range("N8").Select
    ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-13]),""Null"",VALUE(RC[-13]))"
    Range("N9").Select
 
Last edited by a moderator:

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
Does this array
Code: 
[COLOR=#333333](Array("NY Reg", "NY Lic & Reg", "Skip in Ramco", "Annual", "90 Day", _[/COLOR]
[COLOR=#333333]"Support", "NY Term", "PA Stmt #", "PA Print Card Corp", "PA Print Card State", _[/COLOR]
[COLOR=#333333]"Print Card - Mark Complete", "PA Emp Ver", "PA Child Abuse", "NJN", "NJS", _[/COLOR]
[COLOR=#333333]"Cherry Hill"))
[/COLOR]

Represent all the worksheets? Tell us more about what you are trying to do.
 
Upvote 0
.
This macro seems to work here :

Code: 
Option Explicit


Sub MacroOnEachSht()
Dim v As Long, vWSs As Variant
vWSs = Array("NY Reg", "NY Lic & Reg", "Skip in Ramco", "Annual", "90 Day", _
"Support", "NY Term", "PA Stmt #", "PA Print Card Corp", "PA Print Card State", _
"Print Card - Mark Complete", "PA Emp Ver", "PA Child Abuse", "NJN", "NJS", _
"Cherry Hill")
For v = LBound(vWSs) To UBound(vWSs)
Worksheets(vWSs(v)).Activate
    With Worksheets(vWSs(v))
        ActiveWindow.SmallScroll Down:=-15
        Range("F8").Select
        ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-5],EEMstr,2,FALSE),""Not Listed"")"
        Range("G8").Select
        ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[5],Dataset,7,FALSE),""No"")"
        Range("K8").Select
        ActiveCell.FormulaR1C1 = _
        "=CONCAT(RC[-3],"","",""; "",R7C[-2],"":"","" "",RC[-2],"","",R[-1]C10)"
        Range("K8").Select
        ActiveCell.FormulaR1C1 = _
        "=CONCAT(RC[-3],"","",""; "",R7C[-2],"":"","" "",RC[-2],"","",R7C10,"":"","" "",RC[-1])"
        Range("L8").Select
        ActiveCell.FormulaR1C1 = "=CONCAT(RC[-11],"" "",RC[-9])"
        Range("M8").Select
        ActiveCell.FormulaR1C1 = "=IFERROR(DATEVALUE(RC[-9]),""Null"")"
        Range("N8").Select
        ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-13]),""Null"",VALUE(RC[-13]))"
        Range("N9").Select
    
    End With
Next v
End Sub


But why are you writing one formula to K8 then overwriting it with another formula ?

Code: 
 Range("K8").Select
        ActiveCell.FormulaR1C1 = _
        "=CONCAT(RC[-3],"","",""; "",R7C[-2],"":"","" "",RC[-2],"","",R[-1]C10)"
        Range("K8").Select
        ActiveCell.FormulaR1C1 = _
        "=CONCAT(RC[-3],"","",""; "",R7C[-2],"":"","" "",RC[-2],"","",R7C10,"":"","" "",RC[-1])"

Or am I missing something ?
 
Upvote 0
rlv01 said:
Does this array
Code: 
[COLOR=#333333](Array("NY Reg", "NY Lic & Reg", "Skip in Ramco", "Annual", "90 Day", _[/COLOR]
[COLOR=#333333]"Support", "NY Term", "PA Stmt #", "PA Print Card Corp", "PA Print Card State", _[/COLOR]
[COLOR=#333333]"Print Card - Mark Complete", "PA Emp Ver", "PA Child Abuse", "NJN", "NJS", _[/COLOR]
[COLOR=#333333]"Cherry Hill"))
[/COLOR]

Represent all the worksheets? Tell us more about what you are trying to do.
Click to expand...

No it does not. There are other worksheets within the workbook that this does not apply to. It is supposed to be applying formula's on all the worksheets specified above.
 
Upvote 0
Logit said:
.
This macro seems to work here :

Code: 
Option Explicit


Sub MacroOnEachSht()
Dim v As Long, vWSs As Variant
vWSs = Array("NY Reg", "NY Lic & Reg", "Skip in Ramco", "Annual", "90 Day", _
"Support", "NY Term", "PA Stmt #", "PA Print Card Corp", "PA Print Card State", _
"Print Card - Mark Complete", "PA Emp Ver", "PA Child Abuse", "NJN", "NJS", _
"Cherry Hill")
For v = LBound(vWSs) To UBound(vWSs)
Worksheets(vWSs(v)).Activate
    With Worksheets(vWSs(v))
        ActiveWindow.SmallScroll Down:=-15
        Range("F8").Select
        ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-5],EEMstr,2,FALSE),""Not Listed"")"
        Range("G8").Select
        ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[5],Dataset,7,FALSE),""No"")"
        Range("K8").Select
        ActiveCell.FormulaR1C1 = _
        "=CONCAT(RC[-3],"","",""; "",R7C[-2],"":"","" "",RC[-2],"","",R[-1]C10)"
        Range("K8").Select
        ActiveCell.FormulaR1C1 = _
        "=CONCAT(RC[-3],"","",""; "",R7C[-2],"":"","" "",RC[-2],"","",R7C10,"":"","" "",RC[-1])"
        Range("L8").Select
        ActiveCell.FormulaR1C1 = "=CONCAT(RC[-11],"" "",RC[-9])"
        Range("M8").Select
        ActiveCell.FormulaR1C1 = "=IFERROR(DATEVALUE(RC[-9]),""Null"")"
        Range("N8").Select
        ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-13]),""Null"",VALUE(RC[-13]))"
        Range("N9").Select
    
    End With
Next v
End Sub


But why are you writing one formula to K8 then overwriting it with another formula ?

Code: 
 Range("K8").Select
        ActiveCell.FormulaR1C1 = _
        "=CONCAT(RC[-3],"","",""; "",R7C[-2],"":"","" "",RC[-2],"","",R[-1]C10)"
        Range("K8").Select
        ActiveCell.FormulaR1C1 = _
        "=CONCAT(RC[-3],"","",""; "",R7C[-2],"":"","" "",RC[-2],"","",R7C10,"":"","" "",RC[-1])"

Or am I missing something ?
Click to expand...

That is an error in the macro that I didn't realize existed until now (Thank you!). However, what do you mean that "It's working here"? Did you make a change that I am not seeing?
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Macro Fill Down Error
Replies
3
Views
829
Michael M
Michael M
A
  • Solved
Changing recorded macro to have dynamic selection of cells when trying to format a sheet.
Replies
4
Views
869
Michael M
Michael M
Belair58
  • Question Question
Loop through all WorkSheets is not working, code review please.
Replies
7
Views
1K
Fluff
Fluff
S
  • Question Question
How can I edit this recorded macro to include all sheets?
Replies
8
Views
858
Michael M
Michael M
P
  • Question Question
Automate creating a worksheet adding rows based on certain criterias (all this in the same workbook)
Replies
1
Views
828
pratikjoshi448
P

Forum statistics

Threads
1,223,888
Messages
6,175,215
Members
452,618
Latest member
Tam84

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top