PDA

View Full Version : [SOLVED] Need help to autofil with VBA



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

p45cal
05-28-2015, 04:12 AM
try (untested):
Sub CheckMatch()
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
Cells(i, 1).EntireRow.Interior.ColorIndex = 6
myCell = Cells(i, "B").Text
mycellRes = myCell - 1
DivAB = Cells(i, "AB").Text
myTot = Round(DivAB / mycellRes)
ValueSought = Cells(i, "A").Value
Set FirstInstance = Columns("A").Find(What:=ValueSought, LookIn:=xlFormulas, LookAt:=xlWhole, SearchFormat:=False)
If Not FirstInstance Is Nothing Then Set RangeToSearch = Range(FirstInstance, Cells.SpecialCells(xlCellTypeLastCell)).Columns(1)
RangeToSearchFirstRow = FirstInstance.Row
RangeToSearchValues = RangeToSearch.Value
Set RangeToUpdate = FirstInstance
For j = 1 To UBound(RangeToSearchValues)
If RangeToSearchValues(j, 1) = ValueSought Then
Set RangeToUpdate = Union(RangeToUpdate, Cells(j - 1 + RangeToSearchFirstRow, "A"))
End If
Next j
RangeToUpdate.Offset(, 19).Value = myTot
End If
Next i
End Sub
Note that it fills column T of all the rows on the sheet with the corresponding value in column A, not just those directly above row i.
As an aside, in your example, is round(88/11) really 7!

p45cal
05-28-2015, 04:19 AM
Oh groan, I see you've cross-posted here: http://www.mrexcel.com/forum/excel-questions/857651-need-help-autofil-visual-basic-applications.html

Please read http://www.excelguru.ca/content.php?184 and just out of netiquette, add the necessary to all your threads on this subject, especially if my offering works for you.

elmnas
05-28-2015, 04:56 AM
Oh groan, I see you've cross-posted here: http://www.mrexcel.com/forum/excel-questions/857651-need-help-autofil-visual-basic-applications.html

Please read http://www.excelguru.ca/content.php?184 and just out of netiquette, add the necessary to all your threads on this subject, especially if my offering works for you.


My apologize.