Help with EXCEL VBA

[webeagle12]

I'm trying to do a simple thing but with me not touching VB in last 10 years I cant figure out how

 

What i'm trying to do is if column B = "plane" and column A numbers are from 1 to 100, then it would spit out in column C = "yes" and if numbers from 100 to 300, then it would spit out "no" in each cell.  There will be bunch of more other data above and below  but i need just to make sure everything that has "plane" in B and number in A next to it will get converted.  Feel stupid to ask because it supposed to be a simple thing for me but can't figure it out anymore. 

 

 

zzzzz	zzzzz
zzzzz	zzzzz
zzzzz	zzzzz
zzzzz	zzzzz
216	plane
217	plane
12	plane
12	plane
221	plane
20	plane
226	plane
223	plane
3	plane
8	plane
25	plane
zzzzz	zzzzz
zzzzz	zzzzz
zzzzz	zzzzz
zzzzz	zzzzz
zzzzz	zzzzz
zzzzz	zzzzz

 

[pqt]

You're sorting through an array (or object) using a key-value pair. I'll see if I can draft something up to show you the functional process in a short bit.

[Dick Montage]

Take a look at:

 

https://www.dropbox.com/s/6qo2n8w6b9kka91/book1.xlsx?dl=0

[limok]

 

This should work...

Sub isPlane()

Dim colA As Integer
Dim colB As String
Dim ws As Worksheet
Dim rng As Range
Dim lastRow As Integer

Set ws = ThisWorkbook.Worksheets("Sheet1")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Set rng = ws.Range("A1:A" & lastRow)

For Each cell In rng
	'assuming colA will always be an integer value
    colA = cell.Value
    colB = ws.Cells(cell.Row, 2)
    
    If colB = "plane" And (colA <= 100 And colA >= 1) Then
        ws.Cells(cell.Row, 3) = "yes"
    ElseIf colB = "plane" And (colA <= 300 And colA >= 100) Then
        ws.Cells(cell.Row, 3) = "no"
    End If
Next

End Sub

edit

Just read through the spec again - column a can be string or numbers. Will need to add a check to see if you can convert cell into integer.

[Dick Montage]

'assuming colA will always be an integer value

 

But it's not?

[limok]

1 minute ago, Nik L said:

 

 

 

But it's not?

yep - just read it fully.

 

Cheers

[Dick Montage]

Just now, limok said:

yep - just read it fully.

 

Cheers

A simple amendment either way

[limok]

Sub isPlane()

Dim colA As Long
Dim colB As String
Dim ws As Worksheet
Dim rng As Range
Dim lastRow As Integer

Set ws = ThisWorkbook.Worksheets("Sheet1")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Set rng = ws.Range("A1:A" & lastRow)

For Each cell In rng
    If IsNumeric(cell.Value) Then
        colA = cell.Value
    Else
        GoTo nextIteration
    End If
    colB = ws.Cells(cell.Row, 2)
    
    If colB = "plane" And (colA <= 100 And colA >= 1) Then
        ws.Cells(cell.Row, 3) = "yes"
    ElseIf colB = "plane" And (colA <= 300 And colA >= 100) Then
        ws.Cells(cell.Row, 3) = "no"
    End If
nextIteration:
Next

End Sub

 

[Dick Montage]

Was my Excel file of any use?  If not, I will pull it (sorry am OCD),

 

Thanks

[webeagle12]

9 hours ago, Nik L said:

Was my Excel file of any use?  If not, I will pull it (sorry am OCD),

 

Thanks

Limok code worked because I have other code that is running behind a scenes.  Thanks!

 

[Dick Montage]

3 hours ago, webeagle12 said:

Limok code worked because I have other code that is running behind a scenes.  Thanks!

 

Ta, will remove file now then