CODEWITHSUNDEEP
excelvba
user3241437
user3241437

Reputation: 3

Using For each loop fails to loop through worksheets

Hi I have been trying to join together several macros to automate a process which involves importing data files from text and copying parts of the data to a "core sheet". My problem arises when i try to use the "For Each loop" to move through the sheets check a condition and if the condition is met run the data extraction macro. Basically the macro does not loop, i researched various solutions but nothing worked. It works fine the first time it is run (usualy the sheet that is displayed meets the conditions so it moves on to the "shima" macro) however then the "core sheet" is displayed and since this does not meet the conditions i just want it to move on to the next sheet but it doesn't. Code is below let me know if any of this is unclear. 

Sub FullAuto()


Call Module1.Myfolderselector 'this macro imports all the text file in a given folder

Dim Msg, Style, Title, Response, MyString
Public ws As Worksheet


Msg = "Yes for Fluorescence, No for UV"
Style = vbYesNo + vbCritical + vbDefaultButton1
Title = "Choose data"
'this parts asks the user which type of Data they want to import

Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
   MyString = "Yes"
Else
   MyString = "No"
End If


For Each ws In Worksheets 'this is the part with issues.

    If MyString = "Yes" And Range("A1").Value <> "Core" Then
    Call Module1.Detector_B_Shima_9_0
    ElseIf MyString = "No" And Range("A1").Value <> "Core" Then
    Call Module1.Detector_A_Shima_9_1

    End If

Next ws

Worksheets.Add(After:=Worksheets(1)).Name = "Plot Sheet"


End Sub 

Sub Detector_B_Shima_9_0()


Cells.Select
    Selection.Find(What:="[LC Chromatogram(Detector B-Ch1)]", After:=ActiveCell _
    , LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False).Activate

ActiveCell.Offset(9, 1).Select
ActiveCell.Value = Range("B20")
Range(ActiveCell, Cells(ActiveCell.Row + 8401, ActiveCell.Column)).Select

Selection.Copy
Sheets("Core Sheet").Select
Range("C3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveCell.EntireColumn.Insert

End Sub

Upvotes: 0

Views: 109

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35863

Try to change this code:

For Each ws In Worksheets 'this is the part with issues.

    If MyString = "Yes" And Range("A1").Value <> "Core" Then
    Call Module1.Detector_B_Shima_9_0
    ElseIf MyString = "No" And Range("A1").Value <> "Core" Then
    Call Module1.Detector_A_Shima_9_1

    End If

Next ws

to the next one

For Each ws In Worksheets 

    If MyString = "Yes" And ws.Range("A1").Value <> "Core" Then
        Call Module1.Detector_B_Shima_9_0
    ElseIf MyString = "No" And ws.Range("A1").Value <> "Core" Then
        Call Module1.Detector_A_Shima_9_1
    End If

Next ws

Note, that I've added ws. before Range("A1") : ws.Range("A1").Value. This little improvment helps VBA to understand, that Range("A1") belongs to worksheet ws.

Upvotes: 2

Related Questions