CODEWITHSUNDEEP
vbaexcel
Cubed
Cubed

Reputation: 35

Excel VBA code does not fully work unless certain parts removed

My issue is when I wrote all the codes, one of it will not work. It does not give an error, but there will be no email generated from the GenerateEmail sub. However, if I remove one of the While Wend code, the others work (I tested them one by one). In short, one of it will not work if all of the While Wend codes are there. I am not sure what is happening. Any help is appreciated. I have tried find similar situations but no success.

My code:

Sub CaseCreation()
Dim subjectTitleEABS As String
Dim subjectTitleCCC As String
Dim subjectTitleRR As String
Dim subjectTitleTERM As String
Dim rowCount As String
rowCount = 2

While Sheets("Extended Abs").Range("B" & CStr(rowCount)).Value <> ""
If Sheets("Extended Abs").Range("A" & CStr(rowCount)).Value = "" Then
subjectTitleEABS = Sheets("Extended Abs").Range("K" & CStr(rowCount)).Value
Call GenerateEmail(subjectTitleEABS)
End If
rowCount = rowCount + 1
Wend

While Sheets("Contractual Conditions Change").Range("B" & CStr(rowCount)).Value <> ""
If Sheets("Contractual Conditions Change").Range("A" & CStr(rowCount)).Value = "" Then
subjectTitleCCC = Sheets("Contractual Conditions Change").Range("L" & CStr(rowCount)).Value
Call GenerateEmail(subjectTitleCCC)
End If
rowCount = rowCount + 1
Wend

While Sheets("Resource Requests").Range("B" & CStr(rowCount)).Value <> ""
If Sheets("Resource Requests").Range("A" & CStr(rowCount)).Value = "" Then
subjectTitleRR = Sheets("Resource Requests").Range("M" & CStr(rowCount)).Value
Call GenerateEmail(subjectTitleRR)
End If
rowCount = rowCount + 1
Wend

While Sheets("Terminations").Range("B" & CStr(rowCount)).Value <> ""
If Sheets("Terminations").Range("A" & CStr(rowCount)).Value = "" Then
subjectTitleTERM = Sheets("Terminations").Range("K" & CStr(rowCount)).Value
Call GenerateEmail(subjectTitleTERM)
End If
rowCount = rowCount + 1
Wend

Upvotes: 0

Views: 45

Answers (1)

Jason Stallard
Jason Stallard

Reputation: 348

Only the first While/wend will ever be executed. If you have 5 rows, for instance, your second one will start at rowCount 6. You need to reset your counter in between each While/Wend.

Upvotes: 1

Related Questions