Better code needed

[ceplane]

I'm at a loss. Is there a way to write this code so it is shorter an
works.... What I'm trying to do is if A2=F2 or G2 or if A2 is betwee
F2 and G2 then B2=H2. I have a column of 100 cells too. I've trie
vlook but is does the opposite of what I'm trying to do.
Thank you for all your help.

Public Sub TA()

If Range("A2").Value = Range("F2").Value Or _
Range("A2").Value = Range("G2").Value Then
Range("B2").Value = Range("H2").Value
Else
Range("B2").Value = ""
End If
If Range("A2").Value > Range("F2").Value And _
Range("A2").Value < Range("G2").Value Then
Range("B2").Value = Range("H2").Value
Else
Range("B2").Value = ""
End If


If Range("A3").Value = Range("F2").Value Or _
Range("A3").Value = Range("G2").Value Then
Range("B3").Value = Range("H2").Value
Else
Range("B3").Value = ""
End If
If Range("A3").Value > Range("F2").Value And _
Range("A3").Value < Range("G2").Value Then
Range("B3").Value = Range("H2").Value
Else
Range("B3").Value = ""
End If


If Range("A4").Value = Range("F2").Value Or _
Range("A4").Value = Range("G2").Value Then
Range("B4").Value = Range("H2").Value
Else
Range("B4").Value = ""
End If
If Range("A4").Value > Range("F2").Value And _
Range("A4").Value < Range("G2").Value Then
Range("B4").Value = Range("H2").Value
Else
Range("B4").Value = ""
End If


End Su

 

[Libby]

Try this

If [A2] = [F2] OR [A2] = [G2] Then
[B2] = [H2]
Else
[B2]=""
End If

'etc

select case [A2]

 

[Bernie Deitrick]

ZePlane,

Range("B2:B100").Formula = "=IF(AND(A2>=F$2,A2<=G$2),H$2,"""")"
Range("B2:B100").Value = Range("B2:B100").Value

The second line is optional.

HTH,
Bernie
MS Excel MVP

 

[Libby]

Sorry I didn't include the "between" F2 & G2 bit
Is F2 always going to be less than G2 ?

 

[George Nicholson]

This is untested aircode, but it may point you in a happier direction


Dim i As Integer
Dim rng As Range

Set rng = Activeworkbook.ActiveWorksheet.Range("A2:H101")

For i = 1 to 100
If (rng.Cells(i,1)>=rng.Cells(i,6)) And
(rng.Cells(i,1)<=rng.Cells(i,7)) Then
' Column A is equal to or between the values of column F & G
' Place the value of Column H in Column B
rng.Cells(i,2) = rng.Cells(i,8)
Else
rng.Cells(i,2) = ""
End if
Next i

--

HTH,
George Nicholson

Remove 'Junk' from return address.

 

[Rick]

Hi,

Review the "cells method" (you should be able to find this
on the internet). Loop through the rows 2 to 4 with your
example (with a For Loop). This compresses your code.
After testing, change the lower and upper boundaries, as
you would like.

If you are still interested in the code I speak of, write
back and I'll take a look at it tomorrow and help you.

Rick

 

[ceplane]

Thanks everyone. Bernie's suggestion worked the best