• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 180
  • Last Modified:

VBA Error

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

0
Seamus2626
Asked:
Seamus2626
1 Solution
 
JuanCarnigliaCommented:
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.
0
 
Rory ArchibaldCommented:
It means you haven't terminated your For...Next loops as there is no Next statement:

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

Open in new window


Note that the second loop will fail as you cannot hide every sheet in a workbook.
0
 
Seamus2626Author Commented:
perfect Rory, thanks for that

Cheers,
Seamus
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now