current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

My Excel Sheet format is similar to this

Name code1  Name Code2  Name       Number    Input1   Input2   
AB           XY        GSABPEXY1    110      BA       BC
BC           BA        GSBCPEBA1    120
CD           CA        GSCDPECA1    13
DC           DA        GSDCPEDA3    140
BC           BA        GSBCPEBA3    15

Question:

  1. I want to obtain the values in the Name column by matching the inputs as in Input1 AND Input2.
  2. I need these matched values to be pasted in another sheet's predefined specific row and columns cells
  3. Using excel function countifs i am counting the number of rows having same repeating values in the Column A (name code1) and B (name code2) e.g. in above BC and BA which gives a count of 2.
  4. I would like to use this value (mentioned in #3) for the loop iteration

I got this code which looks very close but it lacks two things ABC is predefined in INSTR function and it doesn't have the AND operator - i do not know how to do it. I am very new to VBA so please pardon me if I am missing something simple and/or providing less info.

Code:

Sub Hostname()
    Dim K As Long, r As Range, v As Variant
    K = 1
    Dim w1 As Worksheet, w2 As Worksheet
    Set w1 = Sheets("Sheet2")
    Set w2 = Sheets("Sheet3")
    w1.Activate
    For Each r In Intersect(Range("A:A"), ActiveSheet.UsedRange)
        v = r.Value
        If InStr(v, "ABC") > 0 Then
            r.Copy w2.Cells(K, 1)
            K = K + 1
        End If
    Next r
End Sub
share|improve this question
    
Why wouldn't you create a third column with value Input1&Input2 and do a lookup on that? You wouldn't need any vba at all to do a lookup that way. –  yu_ominae Jul 31 '14 at 5:12
    
The thing is the Input1 and Input2 are not enough to do vlookup for getting all the values from the column C as in the sheet above. If you notice if I use only the input BA and BC and use Vlookup I will only get the first GSBCPEBA1 and not the second match GSBCPEBA3. I am not sure if vlookup can do loop like things. –  victorxii Jul 31 '14 at 6:03
    
No, VLookup is not good for doing iterations. I have put some sample code down as an answer based on my understanding of what you are trying to do. –  yu_ominae Jul 31 '14 at 6:28

1 Answer 1

up vote 0 down vote

OK, I think I see what you want to do. How about this?

Sub Hostname()

    Dim codeName1 As String
    Dim codeName2 as string
    Dim count As Integer
    Dim r As Range
    Dim targetRange as Range

    Set targetSheet = ThisWorkbook.Sheets("MyTargetSheetName").Range("AddressOfFirstCellForDataInput")
    codeName1 = "BC"
    codeName2 = "BA"

    For Each r In ActiveSheet.Range("A:A")
        with r
            If .Value2 = codeName1 And .Offset(0, 1).Value2 = codeName2 Then
                targetRange.Offset(count, 0).value = .Offset(0, 2).Value2
                count = count + 1
            ElseIf Len(Trim(.Value2)) = 0 Then
                Exit For
            end if
        End With
    Next

    Call msgbox("Found " & count & "cells matching Name code 1: " & codeName1 & " Name code 2: " & codeName2)
End Sub

This will count the number of cells which match Name Code 1 and Name Code 2. Cand you adapt this to do what you want?

share|improve this answer
    
Sorry, I tried to run this code but it's not giving any output. I changed For Each r In ActiveSheet.Range("A:A") -> For Each r In ActiveSheet.Range("C:C") and ElseIf Len(Trim(.Value2) = 0 Then -> to ElseIf Len(Trim(.Value2)) = 0 Then –  victorxii Jul 31 '14 at 6:36
    
This change ElseIf Len(Trim(.Value2) = 0 Then -> to ElseIf Len(Trim(.Value2)) = 0 Then is good, I forgot a parentheses there. The other change is wrong for what you want I think. I'll modify the code now so it will give you an output. Try it again and let me know. What exactly do you want to output though? –  yu_ominae Jul 31 '14 at 6:42
    
The code I am looking for match whether the values taken from Input1 AND Input2 are present(or contained partially) in any the cells of column C and if they are present there then copy the value of those cells into another sheet say sheet2's predecided cells. The checking of match should be iterative until the value which can be taken from another column (say F and cell F1). Hope this information may help. –  victorxii Jul 31 '14 at 6:42
    
OK, I'll modify it again to do that. –  yu_ominae Jul 31 '14 at 6:44
    
How about now? You will need to modify the name of the target worksheet and the address of the target cell obviously. –  yu_ominae Jul 31 '14 at 6:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.