| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Better code needed
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 -- Message posted from http://www.ExcelForum.com |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Better code needed
Try this
If [A2] = [F2] OR [A2] = [G2] Then [B2] = [H2] Else [B2]="" End If 'etc select case [A2] -----Original Message----- I'm at a loss. Is there a way to write this code so it is shorter and works.... What I'm trying to do is if A2=F2 or G2 or if A2 is between F2 and G2 then B2=H2. I have a column of 100 cells too. I've tried 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 Sub --- Message posted from http://www.ExcelForum.com/ . |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Better code needed
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 "ceplane " wrote in message ... I'm at a loss. Is there a way to write this code so it is shorter and works.... What I'm trying to do is if A2=F2 or G2 or if A2 is between F2 and G2 then B2=H2. I have a column of 100 cells too. I've tried 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 Sub --- Message posted from http://www.ExcelForum.com/ |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Better code needed
Sorry I didn't include the "between" F2 & G2 bit
Is F2 always going to be less than G2 ? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Better code needed
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. "ceplane " wrote in message ... I'm at a loss. Is there a way to write this code so it is shorter and works.... What I'm trying to do is if A2=F2 or G2 or if A2 is between F2 and G2 then B2=H2. I have a column of 100 cells too. I've tried 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 Sub --- Message posted from http://www.ExcelForum.com/ |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Better code needed
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 -----Original Message----- I'm at a loss. Is there a way to write this code so it is shorter and works.... What I'm trying to do is if A2=F2 or G2 or if A2 is between F2 and G2 then B2=H2. I have a column of 100 cells too. I've tried 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 Sub --- Message posted from http://www.ExcelForum.com/ . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Better code needed
Thanks everyone. Bernie's suggestion worked the best
-- Message posted from http://www.ExcelForum.com |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VBA code needed | New Users to Excel | |||
| VBA code help needed | Excel Discussion (Misc queries) | |||
| Code Fix Needed | Excel Programming | |||
| VBA code Help needed | Excel Programming | |||
| code needed | Excel Programming | |||
Linear Mode
