CODEWITHSUNDEEP
excelvbapopulate
Pk Granville
Pk Granville

Reputation: 11

populate sheet A from sheet B - excel VBA

I have the following scenario: I have two worksheets RAW and BOM. What I would like to do is populate the BOM sheet FROM the RAW for certain components.

For example in BOM worksheet I have VXL5-50 (highlighted in yellow). So for that component, I search sheet1--> 'connector type' column and look if that string exists. If it does, then I increment by 1 in the QTY column in the BOM worksheet

Here are the two sheets RAW AND BOM

http://i43.tinypic.com/aos0uu.jpg

http://i43.tinypic.com/j5cxg7.jpg

Upvotes: 0

Views: 2325

Answers (1)

user2230817
user2230817

Reputation: 

Sub test()

Dim rng As Range

Dim dblrow As Double

'shtSearch,shtCoutn are sheet names.

  lastrow = shtSearch.Cells(Rows.Count, 1).End(xlUp).Row

  j = 0

For i = 1 To lastrow
    If InStr(1, shtSearch.Cells(i, 1), "abcd", vbTextCompare) > 0 Then
    'Count the search
        j = j + 1
    End If
Next

Set scrRng = shtCount.Range("A:A")

Set rng = scrRng.Find(What:="abcd", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)

dblrow = Mid(rng.Address, 4, Len(rng.Address) - 3)

shtCount.Cells(dblrow, 2) = j

End Sub

enter image description here

enter image description here

You can modify the above code and use for other criteria.

Upvotes: 1

Related Questions