# Compare Columns and divide

#### elmnas

##### Board Regular
Hello people,

I need help with a code does following:

loops through Column J and K
if the values(text) are equal on the same row
select the Value in Column B and (same row) divided by the value in Column AB (same row) and present the new value -1 in the Column T

and if there is no value in column B after you find same value in J and K skip. that and continue loop

Could someone help me?

I got this code so far but I get an error

Code:
``````Sub test()
Dim i&, j&
Application.ScreenUpdating = False
With WorksheetFunction
For i = 1 To .Max(Cells(Rows.Count, "J").End(xlUp).Row, Cells(Rows.Count, "K").End(xlUp).Row)
If Cells(i, "J") = Cells(i, "K") And Len(Cells(i, "B")) <> 0 Then
j = j + 1
Cells(j, "T") = Cells(i, "B") / Cells(i, "AB") - 1
End If
Next i
End With

End Sub``````

Could someone help me to correct the code otherwise give me a new code?

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### Joe4

Are you trying to subtract one AFTER you do the division?
If so, try adding parentheses, i.e.
Code:
``Cells(j,"T") = (Cells(i, "B") / Cells(i, "AB")) - 1``
Otherwise, I think it may be subtracting the one BEFORE the division. So if Cells(i, "AB") = 1, you will end up trying to divide by zero.

#### elmnas

##### Board Regular
Hi Joe4,

I still get same error on the same line.

#### Joe4

I think you want to add one more part to your IF statement, to check to see if the value you are trying to divide by is not zero (I thought you were originally, but on closer inspection, you are checking the length of Cells(i, "B"), not the value of cells of Cells(i, "AB").

So try changing that if statement to:
Code:
``If Cells(i, "J") = Cells(i, "K") And Len(Cells(i, "B")) <> 0 [COLOR=#ff0000][B]And Cells(i, "AB") <> 0[/B][/COLOR] Then``

#### elmnas

##### Board Regular
I think you want to add one more part to your IF statement, to check to see if the value you are trying to divide by is not zero (I thought you were originally, but on closer inspection, you are checking the length of Cells(i, "B"), not the value of cells of Cells(i, "AB").

So try changing that if statement to:
Code:
``If Cells(i, "J") = Cells(i, "K") And Len(Cells(i, "B")) <> 0 [COLOR=#ff0000][B]And Cells(i, "AB") <> 0[/B][/COLOR] Then``

I don't get any script errors,

but I dont get the result written in column T

Why?

#### Joe4

You won't, in the case where Cells(i, "AB") = 0. That would cause a division by zero error. So by adding what we did to the IF statement, it will skip over those instances.

I thought that is what you said you wanted to do, skip that instance and continue your loop.

#### elmnas

##### Board Regular
here is a new code

Code:
``````Sub test()
Dim i&, j&
Application.ScreenUpdating = False
With WorksheetFunction
For i = 1 To .Max(Cells(Rows.Count, "J").End(xlUp).Row, Cells(Rows.Count, "K").End(xlUp).Row)
If Cells(i, "J") = Cells(i, "K") And Len(Cells(i, "B")) <> 0 And Cells(i, "AB") <> 0 Then
j = j + 1
Cells(j, "T") = Cells(i, "B") / Cells(i, "AB") - 1
End If
Next i
End With

End Sub``````

I get results in Column T but something is very wrong see result

in Column J and K I got IND and IND then take value in Column B same row = 88 -1 / 12 present it in Column T same row

Last edited:

I mailed u

#### Joe4

Your link isn't working for me. Unless it is sensitive information, you should just post it here in the thread instead of trying to send it via PM.

Can't you just tell me what is in those cells for one of these rows that isn't working the way that you want?

Replies
2
Views
148
Replies
10
Views
214
Replies
0
Views
71
Replies
14
Views
362
Replies
2
Views
109

1,195,848
Messages
6,011,946
Members
441,656
Latest member
oo3

### 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.

### Which adblocker are you using?

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

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