Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Difficulty
I have this code, where upon in B1 and C1 are numbers entered such as
1, 1 Line code: --- Sheet1.Range("B1").Value = Sheet1.Range("B1").Value + 1 increases 1,1 to 2,1 to 3,1 .. etc. which is OK However, This Line of Code stays static and does not increase ---- Range("AQ3").Value = Count Question is how can "AQ3" increase as "B1" increases? So 2,1 would be "AQ4", and 3,1 would be "AQ5" .. etc. ---------------------------------------------------------------- Code Sub GetCountNow() Dim No1 As Single Dim No2 As Single Dim NumberOfRows As Single Dim RowRange As Range Dim Count As Single Dim Counter As Integer No1 = [B1] No2 = [C1] NumberOfRows = Range("B3", Range("B" & Rows.Count). _ End(xlUp).Address).Rows.Count Count = 0 Set RowRange = Range("B3", "AN3") For Counter = 1 To NumberOfRows If Not (RowRange.Find(What:=No1, LookAt:=xlWhole) Is Nothing) = True And _ Not (RowRange.Find(What:=No2, LookAt:=xlWhole) Is Nothing) = True Then _ Count = Count + 1 Set RowRange = RowRange.Offset(1) Next Range("AQ3").Value = Count Sheet1.Range("B1").Value = Sheet1.Range("B1").Value + 1 End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Difficulty
smandula,
I'm not sure, but it might help to qualify all your ranges, like you did with some, e.g.: Sheet1.Range("AQ3").Value = Count hth, Doug "smandula" wrote in message ups.com... I have this code, where upon in B1 and C1 are numbers entered such as 1, 1 Line code: --- Sheet1.Range("B1").Value = Sheet1.Range("B1").Value + 1 increases 1,1 to 2,1 to 3,1 .. etc. which is OK However, This Line of Code stays static and does not increase ---- Range("AQ3").Value = Count Question is how can "AQ3" increase as "B1" increases? So 2,1 would be "AQ4", and 3,1 would be "AQ5" .. etc. ---------------------------------------------------------------- Code Sub GetCountNow() Dim No1 As Single Dim No2 As Single Dim NumberOfRows As Single Dim RowRange As Range Dim Count As Single Dim Counter As Integer No1 = [B1] No2 = [C1] NumberOfRows = Range("B3", Range("B" & Rows.Count). _ End(xlUp).Address).Rows.Count Count = 0 Set RowRange = Range("B3", "AN3") For Counter = 1 To NumberOfRows If Not (RowRange.Find(What:=No1, LookAt:=xlWhole) Is Nothing) = True And _ Not (RowRange.Find(What:=No2, LookAt:=xlWhole) Is Nothing) = True Then _ Count = Count + 1 Set RowRange = RowRange.Offset(1) Next Range("AQ3").Value = Count Sheet1.Range("B1").Value = Sheet1.Range("B1").Value + 1 End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Difficulty with Sum+IF Function | Excel Worksheet Functions | |||
Function Difficulty | Excel Discussion (Misc queries) | |||
Calling Sub difficulty | Excel Programming | |||
Difficulty with MOD operator when using Mod 1 | Excel Programming | |||
AutoFill difficulty | Excel Programming |