We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

VBA Error

Seamus2626
Seamus2626 asked
on
Medium Priority
190 Views
Last Modified: 2012-05-11
Hi,

I have attached code below. I added some code to unhide and hide tabs at the beginning of the code,



   For Each objSht In ActiveWorkbook.Sheets
      objSht.Visible = xlSheetVisible
Worksheets("File Import").Activate
Call Worksheets("File Import").cmdAllFiles_Click
Worksheets("Screen").Activate
   For Each objSht In ActiveWorkbook.Sheets
      objSht.Visible = xlSheetVeryHidden
Exit Sub

On doing this i receive the error

"For control Variable already in use"

What does this mean and how can i get around it?

Thanks
Seamus
Private Sub CommandButton1_Click()



   For Each objSht In ActiveWorkbook.Sheets
      objSht.Visible = xlSheetVisible
Worksheets("File Import").Activate
Call Worksheets("File Import").cmdAllFiles_Click
Worksheets("Screen").Activate
   For Each objSht In ActiveWorkbook.Sheets
      objSht.Visible = xlSheetVeryHidden
Exit Sub



''Application.ScreenUpdating = False
'''Call CashNostro

''Sheets("Cash Nostros").Range("B9").QueryTable.Refresh BackgroundQuery:=False





  ' With Sheets("Cash Nostros")
  ' .Range("A2", .Cells(.Rows.Count, "A").End(xlUp).Offset(1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  ' End With




Dim r As Long

With Sheets("Cash Nostros")
    r = .Range("A" & Rows.Count).End(xlUp).Row
    
    
    
    .Range("E2").Formula = "=IF(B2=""Time"",C2,"""")"
    .Range("F2").Formula = "=IF(B2=""Time"",C1,"""")"
    .Range("G2").Formula = "=IF(F2<>"""",A2,"""")"
    .Range("H2").Formula = "=IF(G2<>"""",VLOOKUP(G2,'Mapping Table'!A:B,2,0),"""")"
    .Range("E2:H2").Copy .Range("E2:H" & r)

End With



Sheets("Merit").Range("B9").QueryTable.Refresh BackgroundQuery:=False




   With Sheets("Merit")
   .Range("A2", .Cells(.Rows.Count, "A").End(xlUp).Offset(1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
   End With






With Sheets("Merit")
    r = .Range("A" & Rows.Count).End(xlUp).Row
    
    
    
    .Range("E2").Formula = "=IF(B2=""Time"",C2,"""")"
    .Range("F2").Formula = "=IF(B2=""Time"",C1,"""")"
    .Range("G2").Formula = "=IF(F2<>"""",A2,"""")"
    .Range("H2").Formula = "=IF(G2<>"""",VLOOKUP(G2,'Mapping Table'!A:B,2,0),"""")"
    .Range("E2:H2").Copy .Range("E2:H" & r)

End With

Sheets("Internal AC's").Range("B9").QueryTable.Refresh BackgroundQuery:=False




   With Sheets("Internal AC's")
   .Range("A2", .Cells(.Rows.Count, "A").End(xlUp).Offset(1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
   End With






With Sheets("Internal AC's")
    r = .Range("A" & Rows.Count).End(xlUp).Row
    
    
    
    .Range("E2").Formula = "=IF(B2=""Time"",C2,"""")"
    .Range("F2").Formula = "=IF(B2=""Time"",C1,"""")"
    .Range("G2").Formula = "=IF(F2<>"""",A2,"""")"
    .Range("H2").Formula = "=IF(G2<>"""",VLOOKUP(G2,'Mapping Table'!A:B,2,0),"""")"
    .Range("E2:H2").Copy .Range("E2:H" & r)



End With

Sheets("Stock").Range("B9").QueryTable.Refresh BackgroundQuery:=False




   With Sheets("Stock")
   .Range("A2", .Cells(.Rows.Count, "A").End(xlUp).Offset(1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
   






With Sheets("Stock")
    r = .Range("A" & Rows.Count).End(xlUp).Row
    
    
    
    .Range("E2").Formula = "=IF(B2=""Time"",C2,"""")"
    .Range("F2").Formula = "=IF(B2=""Time"",C1,"""")"
    .Range("G2").Formula = "=IF(F2<>"""",A2,"""")"
    .Range("H2").Formula = "=IF(G2<>"""",VLOOKUP(G2,'Mapping Table'!A:B,2,0),"""")"
    .Range("E2:H2").Copy .Range("E2:H" & r)
    
    End With


Sheets("DCDIV").Range("B9").QueryTable.Refresh BackgroundQuery:=False




With Sheets("DCDIV")
.Range("A2", .Cells(.Rows.Count, "A").End(xlUp).Offset(1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
   






With Sheets("DCDIV")
    r = .Range("A" & Rows.Count).End(xlUp).Row
    
    
    
    .Range("E2").Formula = "=IF(B2=""Time"",C2,"""")"
    .Range("F2").Formula = "=IF(B2=""Time"",C1,"""")"
    .Range("G2").Formula = "=IF(F2<>"""",A2,"""")"
    .Range("H2").Formula = "=IF(G2<>"""",VLOOKUP(G2,'Mapping Table'!A:B,2,0),"""")"
    .Range("E2:H2").Copy .Range("E2:H" & r)
    





  End With

Sheets("Crest Claims").Range("B9").QueryTable.Refresh BackgroundQuery:=False

   With Sheets("Crest Claims")
   .Range("A2", .Cells(.Rows.Count, "A").End(xlUp).Offset(1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
   






With Sheets("Crest Claims")
    r = .Range("A" & Rows.Count).End(xlUp).Row
    
    
    
    .Range("E2").Formula = "=IF(B2=""Time"",C2,"""")"
    .Range("F2").Formula = "=IF(B2=""Time"",C1,"""")"
    .Range("G2").Formula = "=IF(F2<>"""",A2,"""")"
    .Range("H2").Formula = "=IF(G2<>"""",VLOOKUP(G2,'Mapping Table'!A:B,2,0),"""")"
    .Range("E2:H2").Copy .Range("E2:H" & r)
    
    
    Sheets("Sophis Fiscal").Range("B9").QueryTable.Refresh BackgroundQuery:=False

   With Sheets("Sophis Fiscal")
   .Range("A2", .Cells(.Rows.Count, "A").End(xlUp).Offset(1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
   






With Sheets("Sophis Fiscal")
    r = .Range("A" & Rows.Count).End(xlUp).Row
    
    
    
    .Range("E2").Formula = "=IF(B2=""Time"",C2,"""")"
    .Range("F2").Formula = "=IF(B2=""Time"",C1,"""")"
    .Range("G2").Formula = "=IF(F2<>"""",A2,"""")"
    .Range("H2").Formula = "=IF(G2<>"""",VLOOKUP(G2,'Mapping Table'!A:B,2,0),"""")"
    .Range("E2:H2").Copy .Range("E2:H" & r)



Application.DisplayAlerts = True


End With

MsgBox "New Data Imported"

End With

End With
End With
End With
End With
End Sub





Private Sub CommandButton8_Click()

Call deletePic
Sheets("Cash Chart").Visible = True
Sheets("Cash Chart").Select

    
    ActiveWindow.ActiveChart.CopyPicture
    Sheets("Cash Chart").Visible = False
    Sheets("Screen").Select
    Range("C14").Select
    ActiveSheet.Paste
    
    
End Sub

Open in new window

Comment
Watch Question

This means that you are using the same variable name for nested algorithms.

For instance:

   for a in b
     for a in c
     loop
   loop

Tidy up your code and it will become clear.
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
perfect Rory, thanks for that

Cheers,
Seamus
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.