Macro help

[Guest]

I was wondering if the following can be done

I want to create a macro that will look in column C for a letter or a
combination of letters and then when it finds the particular letter(s), I
would like it to go to the corresponding numeric value in column A. Then
search all of column A for any like numeric values and then show me the
corresponding letter(s) that are associated with that value.

Example:

Column A Column B Column C
5 x ab
9 x cdi
5 x b
9 x grt

So if I were to enter the letters “ab†into my macro it would tell me the
value is “5†and the other letter that contains that value is “bâ€.

Is this possible to do??

 

[Guest]

Nick,

Try this:

Sub test()
Call MatchCols("cd")
End Sub

Sub MatchCols(mValue)

Dim res, n As Long, c, firstaddress As String
Dim Letters() As String, num As Integer, i As Integer

n = 0
res = Application.Match(mValue, Range("c1:C5"), 0) ' <=== Change range

If Not IsError(res) Then
num = Cells(res, 3).Offset(0, -2)
With Worksheets(1).Range("a1:a5") ' <== Changr range
Set c = .Find(num, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
n = n + 1
ReDim Preserve Letters(n)
Letters(n) = c.Offset(0, 2).Value
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End With
Else
MsgBox mValue & " not found"
End If

For i = 1 To n
Debug.Print Letters(i)
Next i

End Sub

 

[Guest]

You can give this a try...

Sub FindAll()
Dim wksToSearch As Worksheet
Dim wksToPaste As Worksheet
Dim rngToSearch As Range
Dim rngToPaste As Range
Dim rngCurrent As Range
Dim rngFirst As Range
Dim rngFound As Range

Set wksToSearch = Sheets("Sheet1")
Set rngToSearch = wksToSearch.Columns(3)

Set rngCurrent = rngToSearch.Find(InputBox("What letter?"))
If Not rngCurrent Is Nothing Then
Set rngToSearch = wksToSearch.Columns(1)
Set rngCurrent = rngToSearch.Find(rngCurrent.Offset(0, -2))
Set rngFound = rngCurrent
Set rngFirst = rngCurrent
Do
Set rngFound = Union(rngFound, rngCurrent)
Set rngCurrent = rngToSearch.FindNext(rngCurrent)
Loop Until rngCurrent.Address = rngFirst.Address
Set wksToPaste = Worksheets.Add
rngFound.EntireRow.Copy wksToPaste.Range("A1")
Else
MsgBox "That letter was not found"
End If

End Sub