non-continuous range not working

TheMadTree

New Member
Joined
Mar 23, 2015
Messages
25
Good afternoon guys,

I am working with a nested for each loop to insert data from one workbook to another.
It seems that it's stuck to the first value in the non-continuous range. In the Pineapple workbook range A14 is Banana and range A21 is Apple and it never skips to apple.

Can someone please help me :S

This is my code:

Sub Banana()

Dim Weeknumber As Long
Dim ws As Worksheet
Dim xlrange As Range
Dim xlrange2 As Range
Dim xlrange3 As Range
Dim xlcell2 As Range
Dim xlcell3 As Range
Dim Salesorg As Integer
Dim Score As Variant

Set xlrange2 = Workbooks("Pineapple.xlsm").Sheets("Overview").Range("A14,A21")
For Each xlcell2 In xlrange2
Score = xlcell2.Offset(0, 1).Value

If xlrange2 = "Banana" _
Then Set xlrange3 = Workbooks("Orange.xlsm").Sheets("Pear").Range("A4:A56")
If xlrange2 = "Apple" _
Then Set xlrange3 = Workbooks("Orange.xlsm").Sheets("Salad").Range("A4:A56")

For Each xlcell3 In xlrange3
If xlcell3.Value = Weeknumber Then
xlcell3.Offset(0, 7).Value = Score


End If


Next xlcell3


Next xlcell2

Thank you!
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Hi,

Instead of:
Code:
If xlrange2 = "Banana" _
shouldn't you be using:
Code:
If xlcell2 = "Banana" _
etc.
 
Upvote 0

Forum statistics

Threads
1,226,730
Messages
6,192,702
Members
453,748
Latest member
akhtarf3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top