CODEWITHSUNDEEP
excelvbamatch
Phalanx
Phalanx

Reputation: 1217

VBA - Excel : spot a specific number in a column

I have in my excel file a column filed with words and numbers like this: enter image description here

My goal is to have my program analyzing the elements of this column. Right now, I am trying to identify the postal code which is, in my case, a four figures numbers (here 1121).

So I want to loop from the first to the last element, and have a matching method saying something like "if this element is a number and has four figures, then...". I don't know if this is possible in VBA and my researches have been quite unproductive as google-ing my key words for this query leads to results which doesn't relate to what I'm doing.

I guess the structure would look like this : 

For i = 2 To lRowD
Range("D" & i).Activate
myResult = xxxxxxxxxxxxxxxxxx 'SOMETHING IDENTIFYING FOUR FIGURES NUMBERS

If myResult Then 'if Metro Manila in the address
  Range("A20").Value = ActiveCell.Value  'put the Zip Code in another cell
Else

End If
 Next i

Upvotes: 0

Views: 144

Answers (1)

Doug Glancy
Doug Glancy

Reputation: 27478

Does this do what you want?

Sub ProcessColumn()
Dim ws As Excel.Worksheet
Dim lRowD As Long
Dim cell As Excel.Range

Set ws = ActiveSheet
With ws
    lRowD = .Range("D" & .Rows.Count).End(xlUp).Row
    For Each cell In .Range("D2:D" & lRowD).Cells
        If Len(cell.Value) = 4 And (Val(cell.Value) = cell.Value) Then
            ws.Range("A20").Value = cell.Value  'put the Zip Code in another cell
        Else
            'do something else?
        End If
    Next cell
End With
End Sub

Upvotes: 1

Related Questions