Modify code

[Damo10]

Hi,

I have the following code that looks at a list of codes and if a mtach is found changes the values of other cells on that row.

I currently have the list of codes in the same workbook but want to move it to another workbook, I have tried to change the code but cant get it to work when I move it, can anyone help and also make any other suggestions on how to imptove the code as I am still learning?

The code currently runs on sheet Data, I would like to move the code list to sheet Base

Regards Damian

Sub Naturals()
Dim Rng As Range
Dim Rng1 As Range
Dim C As Range
Dim D As Range
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Set Rng = Range(Range("CO2"), Range("CO" & Rows.count).End(xlUp))
Set Rng1 = Range(Range("O2"), Range("O" & Rows.count).End(xlUp))
For Each D In Rng
For Each C In Rng1
If C.Value = D.Value Then C.Offset(, -7).Value = "Natural"
If C.Value = D.Value Then C.Offset(, -6).Value = "Natural"
If C.Value = D.Value Then C.Offset(, -5).Value = 0
If C.Value = D.Value Then C.Offset(, -4).Value = 0
If C.Value = D.Value Then C.Offset(, -3).Value = 1
If C.Value = D.Value Then C.Offset(, -1).Value = 1
Next C
Next D
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

 

[hiker95]

Damo10,


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub Naturals()
Dim Rng As Range
Dim Rng1 As Range
Dim C As Range
Dim D As Range
With Application
  .ScreenUpdating = False
  .Calculation = xlCalculationManual
End With
Set Rng = Range(Range("CO2"), Range("CO" & Rows.Count).End(xlUp))
Set Rng1 = Range(Range("O2"), Range("O" & Rows.Count).End(xlUp))
For Each D In Rng
  For Each C In Rng1
    If C.Value = D.Value Then C.Offset(, -7).Value = "Natural"
    If C.Value = D.Value Then C.Offset(, -6).Value = "Natural"
    If C.Value = D.Value Then C.Offset(, -5).Value = 0
    If C.Value = D.Value Then C.Offset(, -4).Value = 0
    If C.Value = D.Value Then C.Offset(, -3).Value = 1
    If C.Value = D.Value Then C.Offset(, -1).Value = 1
  Next C
Next D
With Application
  .Calculation = xlCalculationAutomatic
  .ScreenUpdating = True
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the Naturals macro.

 

[Damo10]

Hi,

Thanks for your reply.

I might not have been specific enough in my first post. I have a list of codes from cell CO2 in worksheet Data, I would like to move this list to sheet Base from cell A2, the code thats sets Rng will need to be modified which is what I am having trouble with.

Sub Naturals()
Dim Rng As Range
Dim Rng1 As Range
Dim C As Range
Dim D As Range
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Set Rng = Range(Range("CO2"), Range("CO" & Rows.count).End(xlUp)) [COLOR=red][B]Modify this line[/B][/COLOR]
Set Rng1 = Range(Range("O2"), Range("O" & Rows.count).End(xlUp))
For Each D In Rng 
For Each C In Rng1
If C.Value = D.Value Then C.Offset(, -7).Value = "Natural"
If C.Value = D.Value Then C.Offset(, -6).Value = "Natural"
If C.Value = D.Value Then C.Offset(, -5).Value = 0
If C.Value = D.Value Then C.Offset(, -4).Value = 0
If C.Value = D.Value Then C.Offset(, -3).Value = 1
If C.Value = D.Value Then C.Offset(, -1).Value = 1
Next C
Next D
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

 

[Damo10]

Can anyone help with this?

Regards Damian