elmnas
05-28-2015, 12:33 AM
Hi all Excel Members,
I have a function that checks if the text content in two textcells on same row on column J and column K is the same.
when two equal cells find same row (column J and column K), go to Column A check which number is (for example, in this case, "34234") select column B the same row "12" then comes a math formula, that ads a new value in Column T same row,
I want now
Autofil in this example the value "7" in all rows on column T to the first instance of (34234(there can by over 100000 numbers))
I got this code so far
Sub CheckMatch()
' the code look in column J after a equal Value but in Column K and if found
' Set interior color index = yellow
' select Cell in Column B same row, - 1 then divide by sum from column AB then present a new result in Column T same row
For i = 2 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
If Cells(i, "J") = Cells(i, "K") And Len(Cells(i, "B")) <> 0 And Cells(i, "AB") <> 0 Then
'Dim DivAB As String
'Rows(i).Select
Cells(i, 1).EntireRow.Interior.ColorIndex = 6
'Cells(i, "B").Select
myCell = Cells(i, "B").Text
mycellRes = myCell - 1
DivAB = Cells(i, "AB").Text
myTot = Round(DivAB / mycellRes)
' presentera i kolumn T
'mycellSel = Cells(i, "T").Select
'ActiveCell.Value = myTot
Cells(i, 20) = myTot
'MsgBox myTot
End If
Next i
End Sub
here is original file:
http://i.imgur.com/24SD2hm.png
Here is the result I want:
http://i.imgur.com/75TKnnl.png
Could someone help me out ?
Thank you in advance
I have a function that checks if the text content in two textcells on same row on column J and column K is the same.
when two equal cells find same row (column J and column K), go to Column A check which number is (for example, in this case, "34234") select column B the same row "12" then comes a math formula, that ads a new value in Column T same row,
I want now
Autofil in this example the value "7" in all rows on column T to the first instance of (34234(there can by over 100000 numbers))
I got this code so far
Sub CheckMatch()
' the code look in column J after a equal Value but in Column K and if found
' Set interior color index = yellow
' select Cell in Column B same row, - 1 then divide by sum from column AB then present a new result in Column T same row
For i = 2 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
If Cells(i, "J") = Cells(i, "K") And Len(Cells(i, "B")) <> 0 And Cells(i, "AB") <> 0 Then
'Dim DivAB As String
'Rows(i).Select
Cells(i, 1).EntireRow.Interior.ColorIndex = 6
'Cells(i, "B").Select
myCell = Cells(i, "B").Text
mycellRes = myCell - 1
DivAB = Cells(i, "AB").Text
myTot = Round(DivAB / mycellRes)
' presentera i kolumn T
'mycellSel = Cells(i, "T").Select
'ActiveCell.Value = myTot
Cells(i, 20) = myTot
'MsgBox myTot
End If
Next i
End Sub
here is original file:
http://i.imgur.com/24SD2hm.png
Here is the result I want:
http://i.imgur.com/75TKnnl.png
Could someone help me out ?
Thank you in advance