0

This is probably a silly question but I cant seem to get it to work, A nice person on here helped me with getting my initial code to work.

However I have been asked for a change and I cant seem to get it to work.

My data comes in two tables so when I use the current code It provides me with the header row in my next table, the only way around this is to look up rows 6-28 and then I need it to jump and look up rows 35-50 (if it looks at anything in between this I get my header row appearing).

I have tried to update the code below to get it to reference these two blocks separately but it doesn't seem to like it.

*****What my raw data looks like*****

What my raw data looks like

*****When I run the current code the results I am getting*****

When I run the current code the results I am getting

Below is the code I have tried to alter to get it to look at the two areas separately

Any help would be greatly appreciated.

Sub BUTTON5TEST_Click()

Dim c As Range
Dim d As Range
Dim j As Integer
Dim Source As Worksheet
Dim Target As Worksheet

' Change worksheet designations as needed
 Set Source = ActiveWorkbook.Worksheets("Wheel Diameters")
Set Target = ActiveWorkbook.Worksheets("Wheel Diameters League")

j = 3     ' Start copying to row 3 in target sheet

For i = 1 To 3 'Number of ¿wees?
For Each c In Source.Range(Cells(6, 5 * i - 2), Cells(28, 5 * i + 1))
For Each d In Source.Range(Cells(35, 5 * i - 2), Cells(50, 5 * i + 1))
            If c.Text <= 800 Then

        Target.Cells(j, 1) = Source.Cells(c.Row, 5 * i - 3)
        Target.Cells(j, 2) = Source.Cells(5, c.Column)

       j = j + 1
    End If

Next c
Next d
Next i

End Sub
Community
  • 1
  • 1
Paula
  • 229
  • 1
  • 2
  • 13
  • Hi again. Unlike your previous questions, this one is not clear. If your problem persists after 4 days of asking the question, please clarify. – CMArg Sep 05 '16 at 10:56
  • @CMArg I have updated my question I hope this explains better – Paula Sep 08 '16 at 14:08

1 Answers1

1

Excuse me, but I still can't get what you want. It would help if 1) your example pictures contains Row numbers and Column letters, and if 2) the results your're showing corresponds to the picture's data (for example 3302). If not we're left guessing too many things. Anyway, I tried a code. Not for responding a question I'm not fully understanding, but trying to move one step forward. Basically, I tried to union your two ranges. Also, you should pay particular atention to the line If c.Text <= 800 Then: it seems odd to check if a string (.text) is less than 800. And finally, make shure that your defined range excludes headings (I guess thats why you are getting those "empty" rows between the 3000's and the 4000's in the results).

Sub BUTTON5TEST_Click()

Dim c As Range
Dim j As Integer
Dim Source As Worksheet
Dim Target As Worksheet

'Change worksheet designations as needed
Set Source = ActiveWorkbook.Worksheets("Wheel Diameters")
Set Target = ActiveWorkbook.Worksheets("Wheel Diameters League")

j = 3     ' Start copying to row 3 in target sheet

For i = 1 To 3 'Number of ¿wees?
    For Each c In Union(Source.Range(Cells(6, 5 * i - 2), Cells(28, 5 * i + 1)), Source.Range(Cells(35, 5 * i - 2), Cells(50, 5 * i + 1)))

        If c.Text <= 800 Then

            Target.Cells(j, 1) = Source.Cells(c.Row, 5 * i - 3)
            Target.Cells(j, 2) = Source.Cells(5, c.Column)

           j = j + 1
        End If

    Next c

Next i

End Sub
CMArg
  • 1,525
  • 3
  • 13
  • 28
  • that does exactly what I needed I couldnt work out what code I needed to use to create the join between the two lines of code. The 'In Union' part works perfectly. Going to have to make a note of that for future reference. the 800 is because I only need the information returned from the cells that are 800 or less. If i have to post any more questions I will make sure to include row and column letters/numbers.Thanks again. – Paula Sep 09 '16 at 12:29
  • Glad it helps. In reference to `c.Text`, I know it is doing its job, but the rationale seems odd, since you explicitly compare a string to a number. See [here](http://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2). – CMArg Sep 09 '16 at 13:38