Compile error: Statements and labels invalid between Select Case and first Case

[Unicode]

My VBA script errors when I try to insert data from Sheet1 into Sheet2. I get the following error: "Compile error: Statements and labels invalid between Select Case and first Case". I removed the second "select Case" all works okay, listed below, VBA script. Wondering what else should be included in Select Case script?




Sub CopyFromSheet1()
Dim i As Long
For i = 1 To Sheet1.Cells(Sheet1.Rows.Count, 6).End(xlUp).Row ' Last Cell of Column F
Select Case CStr(Sheet1.Cells(i, 3).Value) ' Looks at the Value in Column C
Select Case CStr(Sheet1.Cells(i, 4).Value) ' Looks at the Value in Column D
Case "Due To"
Sheet2.Cells(22, 3).Value = Sheet1.Cells(i, 6).Value
Case "TOTAL 1"
Sheet2.Cells(23, 3).Value = Sheet1.Cells(i, 6).Value
Case "TOTAL 2"
Sheet2.Cells(24, 3).Value = Sheet1.Cells(i, 6).Value
End Select
Next i
End Sub

 

[Joe4]

I am not sure what you are trying to do, but every "Select Case" line needs a matching "End Select" line.
You have two "Select Case" lines, but only one "End Select".

Also, you don't have any "Case" lines under the first "Select Case" line, so that first one isn't doing anything.

If you can explain to us exactly what it is you are trying to do, we can help you structure it correctly.

 

[Unicode]

Let me check again with "End Select" line.

 

[Unicode]

I tried the following, however, I see no updates or number input within Sheet 2.




Sub CopyFromSheet1()
Dim i As Long
For i = 1 To Sheet1.Cells(Sheet1.Rows.Count, 6).End(xlUp).Row ' Last Cell of Column F
Select Case CStr(Sheet1.Cells(i, 3).Value) ' Looks at the Value in Column C
Case Is = 1
Select Case CStr(Sheet1.Cells(i, 4).Value) ' Looks at the Value in Column D
Case "Due To"
Sheet2.Cells(22, 3).Value = Sheet1.Cells(i, 6).Value
Case "TOTAL 1"
Sheet2.Cells(23, 3).Value = Sheet1.Cells(i, 6).Value
Case "TOTAL 2"
Sheet2.Cells(24, 3).Value = Sheet1.Cells(i, 6).Value
End Select
End Select
Next i
End Sub

 

[Joe4]

The CStr function converts the value to a String. So you would be looking for "1" (with quotes around it, denoting a string), not 1 (which is a numeric value).

Or you could change your first Case line to:

[COLOR=#0000CD]Select Case Sheet1.Cells(i, 3).Value ' Looks at the Value in Column C[/COLOR]

(no need to convert it to a String)

 

[Unicode]

I added your specific details, I get no errors or numbers in Sheet 2.

I am not sure what you are trying to do, but every "Select Case" line needs a matching "End Select" line.
You have two "Select Case" lines, but only one "End Select".

Also, you don't have any "Case" lines under the first "Select Case" line, so that first one isn't doing anything.

If you can explain to us exactly what it is you are trying to do, we can help you structure it correctly.

 

[Unicode]

I see nothing update, even after I removed =1 and set it to ""



Sub CopyFromSheet1()
Dim i As Long
For i = 1 To Sheet1.Cells(Sheet1.Rows.Count, 6).End(xlUp).Row ' Last Cell of Column F
Select Case CStr(Sheet1.Cells(i, 3).Value) ' Looks at the Value in Column C
Case ""
Select Case CStr(Sheet1.Cells(i, 4).Value) ' Looks at the Value in Column D
Case "Due To"
Sheet2.Cells(22, 3).Value = Sheet1.Cells(i, 6).Value
Case "TOTAL 1"
Sheet2.Cells(23, 3).Value = Sheet1.Cells(i, 6).Value
Case "TOTAL 2"
Sheet2.Cells(24, 3).Value = Sheet1.Cells(i, 6).Value
End Select
End Select
Next i
End Sub

I added your specific details, I get no errors or numbers in Sheet 2.

 

[Unicode]

I added "Case Else" and only the first select case listed numbers.



Sub CopyFromSheet1()
Dim i As Long
For i = 1 To Sheet1.Cells(Sheet1.Rows.Count, 6).End(xlUp).Row ' Last Cell of Column F
Select Case CStr(Sheet1.Cells(i, 3).Value) ' Looks at the Value in Column C
Case Else
Select Case CStr(Sheet1.Cells(i, 4).Value) ' Looks at the Value in Column D
Case "Due To"
Sheet2.Cells(22, 3).Value = Sheet1.Cells(i, 6).Value
Case "TOTAL 1"
Sheet2.Cells(23, 3).Value = Sheet1.Cells(i, 6).Value
Case "TOTAL 2"
Sheet2.Cells(24, 3).Value = Sheet1.Cells(i, 6).Value
End Select
End Select
Next i
End Sub

I see nothing update, even after I removed =1 and set it to ""



Sub CopyFromSheet1()
Dim i As Long
For i = 1 To Sheet1.Cells(Sheet1.Rows.Count, 6).End(xlUp).Row ' Last Cell of Column F
Select Case CStr(Sheet1.Cells(i, 3).Value) ' Looks at the Value in Column C
Case ""
Select Case CStr(Sheet1.Cells(i, 4).Value) ' Looks at the Value in Column D
Case "Due To"
Sheet2.Cells(22, 3).Value = Sheet1.Cells(i, 6).Value
Case "TOTAL 1"
Sheet2.Cells(23, 3).Value = Sheet1.Cells(i, 6).Value
Case "TOTAL 2"
Sheet2.Cells(24, 3).Value = Sheet1.Cells(i, 6).Value
End Select
End Select
Next i
End Sub

 

[Unicode]

I set my first case to the following, I only see the first case enter numbers into sheet2.

Select Case Sheet1.Cells(i, 3).Value ' Looks at the Value in Column C

I added "Case Else" and only the first select case listed numbers.



Sub CopyFromSheet1()
Dim i As Long
For i = 1 To Sheet1.Cells(Sheet1.Rows.Count, 6).End(xlUp).Row ' Last Cell of Column F
Select Case CStr(Sheet1.Cells(i, 3).Value) ' Looks at the Value in Column C
Case Else
Select Case CStr(Sheet1.Cells(i, 4).Value) ' Looks at the Value in Column D
Case "Due To"
Sheet2.Cells(22, 3).Value = Sheet1.Cells(i, 6).Value
Case "TOTAL 1"
Sheet2.Cells(23, 3).Value = Sheet1.Cells(i, 6).Value
Case "TOTAL 2"
Sheet2.Cells(24, 3).Value = Sheet1.Cells(i, 6).Value
End Select
End Select
Next i
End Sub

 

[Joe4]

I am not sure what you were trying to do in the last two posts.

Let's go about this another way. Forget all the code for the moment being.
Please explain what your data looks like (maybe post some samples), and then explain what you are trying to do with it and what you want the output to look like (and maybe post an example of that).

Note that you cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.