Compare Columns and divide

[elmnas]

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

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

 

[Joe4]

Are you trying to subtract one AFTER you do the division?
If so, try adding parentheses, i.e.

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]

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:

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]

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:

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

 

[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]

here is a new 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

 

[Joe4]

I cannot see your image. If you wish to post images, see here: http://www.mrexcel.com/forum/about-board/508133-attachments.html

So, for your row, you say that "IND" exists in columns J and K. What is in columns B and AB of this row?

 

[elmnas]

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?