Array Awry

**Whitewater** · 02-08-2007, 02:36 PM

I have adapted an array sub off of the forum where I want to strip away 4 of the 9 columns. I'm using an If & ElseIf decision to detect the column number and it works for the first two columns but hangs up after that. If someone would look at the attached workbook & let me know where I'm going wrong, I would appreciate it.

Sub Macro1()

For i = 1 To LastRow
For j = 1 To LastCol
DataArray(i, j) = Worksheets("Sheet1").Cells(i, j)
Next j
Next i

For i = 1 To LastRow
For j = 1 To 9
If j = 1 Then
Worksheets("Sheet2").Cells(i, j) = DataArray(i, j)

ElseIf j = 3 Then
Worksheets("Sheet2").Cells(i, j - 1) = DataArray(i, j)

ElseIf j = 6 Then
Worksheets("Sheet2").Cells(i, j - 3) = DataArray(i, j)

ElseIf j = 9 Then
Worksheets("Sheet2").Cells(i, j - 4) = DataArray(i, j)
Else:
End If

Next j
Next i

End Sub[/SIZE]

**Leith Ross** · 02-08-2007, 04:20 PM

Hello Whitewater,

When you have to make multiple decisions in your code, like you are doing, it is better to use the Select Case method rather than nesting If statements. It makes the code easier to read and maintain. Let me show you...

Please Login or Register  to view this content.

You can add as may Case statements as you need. Much cleaner don't you think?

Sincerely,
Leith Ross

**Whitewater** · 02-08-2007, 04:27 PM

I had tried the Select Case but I hadn't used the J. I had put in CountCol +1 & that didn't work.

**Whitewater** · 02-08-2007, 04:35 PM

I plugged in the code but it hangs up on the Case 3 saying subscript out of range. See if you get the same result. Thank you.

**MDubbelboer** · 02-08-2007, 05:08 PM

Please Login or Register  to view this content.

i just threw it into the original code, seemed to work for me

**Leith Ross** · 02-08-2007, 05:09 PM

Hello Whitewater,

I'll download your workbook and run the code in the workbook. I let you know what I find.

Sincerely,
Leith Ross

**Whitewater** · 02-08-2007, 05:19 PM

I cut & pasted out of the post but that also hung up on Case 3. I'm double & triple checking the rest of the code to see if I'm missing something else.

**MDubbelboer** · 02-08-2007, 06:30 PM

i ran the macro arrayb() copied and pasted from here

**EDIT**
found the problem. you were running the macro on the second page. it was calculating max rows/max columns from the second page, and when it was trying to get the third data it was out of range as the sheet only had two column headers.

i can't delete my attachment but it's wrong, replace your code with the following:
**EDIT**

Please Login or Register  to view this content.

**Whitewater** · 02-08-2007, 06:35 PM

Thanks again. I tried plugging in the other reply but that still hangs up even though he said it is working in his. I then copied and pasted his whole sub and that didn't work either. So I'm looking forward to whatever anyone finds.

**Leith Ross** · 02-08-2007, 06:36 PM

Hello Whitewater,

I rewrote the macro. It doesn't care which sheet is active. It takes the data from Sheet1 columns "A", "C", "F", and "I" and copies it onto Sheet2 columns A to D.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

**Whitewater** · 02-08-2007, 06:44 PM

Ok, thanks for your patience. I cut and pasted out of your 1st reply and didn't notice the changes in your second reply. It now works very nicely and does exactly what I needed it to do. Thank you Thank you.

Array Awry

LinkBack

Thread Tools

Rate This Thread

Display

Array Awry

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions