Compare Columns and divide

elmnas

Board Regular
Joined
Feb 20, 2015
Messages
206
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

AKsRpJv.png



ps02sVF.png


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?

Thank you in advance
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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.
 
Upvote 0
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
 
Upvote 0
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?

Thank you in advance
 
Upvote 0
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.
 
Upvote 0
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

2015_05_26_14_49_07_Volvo_Statistik_2015_04_17_x.jpg

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:
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

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