I have three fields of data:
1. CaseID: need to Group by
2. Side: either L or R
3. AcctNum
My main Sub: CaptureGrpMacro determines the start(rowStart) and end(rowEnd) rows of each group. The hasLorR function determines if Side is L or R and if so turns flag to True. The Scenario function takes the Scen variable and places in ScenL or ScenR field based on Side being L or R. So if a group(CaseID) has both a Side of L and R, then whatever is in ScenR or ScenL needs to be copied to all rows within that group. This is done by the ProcessGrp function.
So if I Dim out the Call ProcessGrp function, then rowStart, rowEnd, hasL, hasR, ScenR, and ScenL is populated for each row. But when I run the Sub with ProcessGrp function, it processes the first group then hangs up on Row 118 and I have to hit Ctrl-Break to get out of loop. Can someone please take a look at my code and sample data? I can't figure out what is wrong. Thank you very much.
Mike - Option Explicit
-
Private Data_Class As String, Scen As String, ScenR As String, ScenL As String
-
-
Function FindLastRow(LastRow As Long)
-
Dim sht As Worksheet
-
-
Set sht = ThisWorkbook.Worksheets("GrpTest")
-
LastRow = sht.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
-
-
End Function
-
Sub CaptureGrpMacro()
-
'This Function determines the start & end of a CaseID Group
-
Dim rowStart As Integer, rowEnd As Integer
-
Dim HRow As Integer, LastRow As Long
-
Dim hasL As Boolean, hasR As Boolean
-
Dim Side As String, PrevSide As String, NextSide As String
-
-
Call FindLastRow(LastRow)
-
HRow = 10
-
-
rowStart = 0
-
rowEnd = 0
-
hasL = False
-
hasR = False
-
Scen = ""
-
ScenL = ""
-
ScenR = ""
-
-
For HRow = HRow To LastRow
-
Side = Cells(HRow, 2).Value
-
PrevSide = Cells(HRow - 1, 2).Value
-
NextSide = Cells(HRow + 1, 2).Value
-
-
If Side <> "C" Then
-
-
If (PrevSide = "C") And rowStart = 0 Then
-
rowStart = HRow
-
End If
-
-
Call Scenario(HRow, Side, ScenL, ScenR)
-
Call hasLorR(HRow, Side, hasL, hasR)
-
-
If (NextSide = "C") And rowEnd = 0 Then
-
rowEnd = HRow
-
End If
-
-
End If
-
Worksheets("GrpTest").Cells(HRow, 5).Value = rowStart
-
Worksheets("GrpTest").Cells(HRow, 6).Value = rowEnd
-
Worksheets("GrpTest").Cells(HRow, 7).Value = hasL
-
Worksheets("GrpTest").Cells(HRow, 8).Value = hasR
-
-
'If rowStart & rowEnd have been assigned, then reset them
-
If rowStart <> 0 And rowEnd <> 0 Then
-
Call ProcessGrp(HRow, rowStart, rowEnd, ScenL, ScenR, hasL, hasR)
-
rowStart = 0
-
rowEnd = 0
-
hasL = False
-
hasR = False
-
Scen = ""
-
ScenL = ""
-
ScenR = ""
-
End If
-
-
Next HRow
-
-
End Sub
-
Function hasLorR(HRow, Side, hasL, hasR)
-
'Sets hasL and hasR variables based on each row
-
-
If Side = "L" Then
-
hasL = True
-
Else: Side = "R"
-
hasR = True
-
End If
-
-
End Function
-
Function Scenario(HRow, Side, ScenL, ScenR)
-
'Determines Scen variable based on AcctNum field
-
Dim AcctType As String, Z As String, x As String
-
-
'Sets Data Class variable
-
x = Worksheets("GrpTest").Cells(HRow, 1).Value
-
Data_Class = Left(x, 1)
-
-
'Sets AcctType variable
-
Z = Worksheets("GrpTest").Cells(HRow, 3).Value 'Account Number
-
AcctType = Left(Z, 2)
-
-
'Sets Scen variable
-
Select Case AcctType
-
Case "QM": Scen = "QMEMOACT"
-
Case "CC": Scen = "QMEMOACT"
-
Case "BS": Scen = "FINACT"
-
Case "IS": Scen = "FINACT"
-
End Select
-
-
'Populates Scenario for every row
-
If Side = "L" Then
-
ScenR = Scen
-
Worksheets("GrpTest").Cells(HRow, 9) = ScenR 'Side = L
-
Else: Side = "R"
-
ScenL = Scen
-
Worksheets("GrpTest").Cells(HRow, 10) = ScenL 'Side = R
-
End If
-
-
End Function
-
Function ProcessGrp(HRow, rowStart, rowEnd, ScenL, ScenR, hasL, hasR)
-
Dim rng As Range
-
Dim DRow As Integer
-
-
If rowStart < rowEnd And (hasL And hasR = True) Then
-
DRow = 0
-
For HRow = rowEnd To rowStart Step -1
-
For DRow = rowStart To rowEnd
-
Worksheets("GrpTest").Cells(DRow, 9) = ScenR
-
Worksheets("GrpTest").Cells(DRow, 10) = ScenL
-
Next DRow
-
Next HRow
-
End If
-
DRow = 0
-
End Function
-
Line 18 doesn't set the last row variable within the function capture group macro. It only sets a different last row variable that exists only within the find last row function. Once that function returns, that other last row variable disappears.
You can test this by using the debugger to step through the code.
5 2112
The thing that pops out first is that your last row variable isn't populated. Passing a variable into a function creates a new variable within the scope of that function. Setting the value within that function doesn't change the value of the variable outside that function. There might be other things wrong as well but that's the first one I noticed.
Thank you for your reply Rabbit. I'm not sure I understand. The LastRow variable is set in the beginning, or at least I believe it is. The rowEnd variable is also set in the CaptureGrp function, or at least I believe it is. So what are you referring too and where if I may ask. Thank you
Line 18 doesn't set the last row variable within the function capture group macro. It only sets a different last row variable that exists only within the find last row function. Once that function returns, that other last row variable disappears.
You can test this by using the debugger to step through the code.
Rabbit, THANK YOU!!! Your were right. The HRow variable was getting reset to rowStart of the 1st group in the ProcessGrp function, thus continuing to loop through the first group. I set HRow variable to a new variable in ProcessGrp and it worked. I really appreciate your help, I have been stuck on this for far too long. Have a great day!
No problem, good luck on the rest of your project.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Bernhard Sollfrank |
last post by:
Hello,
i have to start a batch process from a dll to transfer
files created by this dll to remote ftp server. I created
a batch file:
ftp -n -v -i -s:D:\batch\ftpcmds.txt ftp-server
and a...
|
by: psb |
last post by:
I make a website for a company where sales reps from all over the country
order literature. This is the 4th case where the exact same thing is
happening in the almost the exact same setup -The...
|
by: Andrea Vincenzi |
last post by:
Help me please, I'm totally stuck!
My Visual Studio 2003 debugger stopped working after I installed
Windows XP Service Pack 2. Here is what happens (with any project,
even a "Hello, world"...
|
by: yogesh |
last post by:
I have compiled kernel 2.4.18 .
I have followed the following sequence ...
as
make mrproper
make xconfig
make dep
make
make modules
make modules_install
then copied system.map and bzimage to...
|
by: Patrick.O.Ige |
last post by:
We have some issues after installing Enterprise Library.
When i run the project once it loads IE succesfully but after closing it and
openeing another IE window
it loads and never finishes so it...
|
by: alsmeirelles |
last post by:
Hi,
I'm building a multithreaded application and I encountered a tiny and
annoying problem. I use a select to wait for data to be read from a
socket, after some reads, the select simply blocks...
|
by: Avaenuha |
last post by:
Hi,
It appears my program can't get past a particular printf() statement.
Code excerpt:
printf("Sales Report\n--------------");
printf("Testing code - pre loop entry");
while(category !=...
|
by: hash4sp |
last post by:
Hello !
I have a problem with pop window on my system.
The application hangs after I close the pop up window.
It works fine on few other systems.
I am using the following line of code to...
|
by: Fiddler2 |
last post by:
I noticed that after running compact/repair, I have to recompile my code for the program not to break the next time I open it. What happens is the auto exec macro runs the main() module, then hangs...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |