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

 

[Unicode]

It is very basic what I am trying to do, let me explain:


- Across Row C38 has data with title name "TOTAL 1"

- Across Row D27 has the first data set with name title "Due To"

- Column ROW "F" has all Totals. VBA script should select "Titles" with "ROW" totals, then transfer digit numbers into Sheet 2 Cells.

 

[Unicode]

It is very basic what I am trying to do, let me explain:




- Across Row C38 has data with title name "TOTAL 1"


- Across Row D27 has the first data set with name title "Due To"


- Column ROW "F" has all Totals. VBA script should select "Titles" with "ROW" totals, then transfer digit numbers into Sheet 2 Cells.

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.

 

[Joe4]

- Across Row C38 has data with title name "TOTAL 1"

- Across Row D27 has the first data set with name title "Due To"

- Column ROW "F" has all Totals. VBA script should select "Titles" with "ROW" totals, then transfer digit numbers into Sheet 2 Cells.

I don't think that is descriptive enough. For one thing, C38 and D27 are not "rows", they are cells. So I am not sure what you are telling me. That those values are in those exact cells? Then what is in the rest of the row?
And transferring digits, from where?

Remember, while the problem is very familiar to you because you have been working on it, all that we have to go in is what you provide us. So the more details the better.
I would love to reproduce your worksheet on my side, so I can work on exactly what you see.
An image would be great (as they say, a bigger is worth a thousand words). But if you don't provide that, you are going to have to explain it in more detail.

 

[Unicode]

Thank you, will upload image very soon.

I don't think that is descriptive enough. For one thing, C38 and D27 are not "rows", they are cells. So I am not sure what you are telling me. That those values are in those exact cells? Then what is in the rest of the row?
And transferring digits, from where?

Remember, while the problem is very familiar to you because you have been working on it, all that we have to go in is what you provide us. So the more details the better.
I would love to reproduce your worksheet on my side, so I can work on exactly what you see.
An image would be great (as they say, a bigger is worth a thousand words). But if you don't provide that, you are going to have to explain it in more detail.

 

[Unicode]

Since I am not able to share an image, or create a link for the Excel sheet image, i will try my best to describe my data and how it looks on the following cells.


1. Column A is empty

2. Column B cells 20 through 37 start with Header title "Account", then account numbers 100, 101, 103, etc... list down from B21 until B35.

Cell B36 reads "TOTAL1"
Cell B37 reads "TOTAL2"


3. Column "D" and "E", cells are merged. Header, name "Description", example: Cash another Cash, Accounts Receivable.
On cell C25 description reads "Due To", on the right side Columns E,F,G are also merged and these provide the Total for each Description on merged column C + D.




So, If I am searching Horizontally through my sheet1 for Total 1, Total 2, and "Due To" my VBA code should select the totals per each title in columns E,F,G displaying down each cell.


My VBA, reads correct however, it is just not taking the "Due To" numbers to the second Sheet2, hope this makes sense.



VBA Cript:




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 Sheet1.Cells(i, 4).Value ' Looks at the Value in Column D
Case Else


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

Case "Due To"
Sheet2.Cells(22, 3).Value = Sheet1.Cells(i, 6).Value
Case "TOTAL1"
Sheet2.Cells(23, 3).Value = Sheet1.Cells(i, 6).Value
Case "TOTAL2"
Sheet2.Cells(24, 3).Value = Sheet1.Cells(i, 6).Value

End Select
End Select

Next i


End Sub

I don't think that is descriptive enough. For one thing, C38 and D27 are not "rows", they are cells. So I am not sure what you are telling me. That those values are in those exact cells? Then what is in the rest of the row?
And transferring digits, from where?

Remember, while the problem is very familiar to you because you have been working on it, all that we have to go in is what you provide us. So the more details the better.
I would love to reproduce your worksheet on my side, so I can work on exactly what you see.
An image would be great (as they say, a bigger is worth a thousand words). But if you don't provide that, you are going to have to explain it in more detail.

 

[Joe4]

I tried recreating from that description, but I am afraid it just isn't clear enough.
Merge cells also can be problematic, especially for VBA (it is generally recommended to never use them, especially when the same thing can be accomplished wuth the "Center Across Selection" formatting option, if you are simply merging cells across multiple columns in single rows - see: https://www.atlaspm.com/toms-tutori...er-across-selection-instead-of-merging-cells/).

One option may be to upload the file to a file sharing site and provide a link here, so we can see the exact file that you are working with (note that I cannot download it from my current location - workplace security policy - but I can from home when I am there later tonight; or someone else may come along and download it and figure it out in the meantime). What typically looks best is if you have the data on the first sheet, then manually make the second sheet look like exactly what you are trying to produce (so make it look the way you want if your macro worked the way you wanted it to).

In the meantime, maybe we can help you help yourself. I think the first problem is your first Case statement:

[COLOR=#333333]Select Case Sheet1.Cells(i, 4).Value ' Looks at the Value in Column D[/COLOR]
[COLOR=#333333]   Case Else[/COLOR]

This doesn't really appear to be doing anything.
What exactly are you trying to check column D for?
And what do you want to happen when that condition is met?

 

[Unicode]

I see, I will attempt to get you an image, I believe you are correct, Select Case Sheet1.Cells(i, 4).Value ' Looks at the Value in Column D is the issue and that is what I need to fix.

I tried recreating from that description, but I am afraid it just isn't clear enough.
Merge cells also can be problematic, especially for VBA (it is generally recommended to never use them, especially when the same thing can be accomplished wuth the "Center Across Selection" formatting option, if you are simply merging cells across multiple columns in single rows - see: https://www.atlaspm.com/toms-tutori...er-across-selection-instead-of-merging-cells/).

One option may be to upload the file to a file sharing site and provide a link here, so we can see the exact file that you are working with (note that I cannot download it from my current location - workplace security policy - but I can from home when I am there later tonight; or someone else may come along and download it and figure it out in the meantime). What typically looks best is if you have the data on the first sheet, then manually make the second sheet look like exactly what you are trying to produce (so make it look the way you want if your macro worked the way you wanted it to).

In the meantime, maybe we can help you help yourself. I think the first problem is your first Case statement:

[COLOR=#333333]Select Case Sheet1.Cells(i, 4).Value ' Looks at the Value in Column D[/COLOR]
[COLOR=#333333]   Case Else[/COLOR]

This doesn't really appear to be doing anything.
What exactly are you trying to check column D for?
And what do you want to happen when that condition is met?

 

[Joe4]

You are getting the value from column D, but then aren't doing anything with it. You are going right to "Case Else", indicating you don't really care what the value in column D is (in which case, you could just get rid of that Select altogether).

If you do care what the value in column D is, then you will want a "Case" statement under it (before the next "Select Case") that checks for a value, i.e.

[COLOR=#333333]Select Case Sheet1.Cells(i, 4).Value ' Looks at the Value in Column D[/COLOR]
    [COLOR=#0000ff]Case 1[/COLOR] ' do this if the value is 1
         do something here