Posts by jpmclaughlin

jpmclaughlin · Apr 14th 2012

Re: Excel Macro Variable Scope Problems

Okay, so I set a watch on the variable and stepped through -- it turns out the code was doing exactly what I wanted. However, since I added a fake Page 1 at the end of everything -- it looped through one more time than intended and reset all except the first page to a blank. Ooops.

Thanks so much for your help, guys.

jpmclaughlin · Apr 14th 2012

Hello all. I am a web designer/developer and am familiar with programming and variables, but I'm totally new to building macros and VB. This forum has answered many of my questions as I've been learning. So please forgive the potentially naive problem I'm having.

Without boring you with too much detail, basically I'm consolidating tens of thousands of patient records from dozens of separate RTF files into a single Excel spreadsheet so that it can be compared with what already exists. (Please don't question the logic or practicality -- it's what the client wants. )

To sort of explain the idea behind the code snippet below, each of the original RTF files represents all the patients from a particular dentist office. And these files get imported complete with page numbers. So the macro I'm writing looks for "Page 1" and knows it's the beginning of a new location. So then I want to grab the location info and apply it to all the patients it finds in that range.

Everything in the code works as expected except that the variable "LocName" doesn't seem to play nice. It works correctly for the very top of the file, but then I get blanks everywhere else.

And if I replace the line where it's supposed to use the "LocName" variable

Code

Selection.Offset(0, 1).Value = LocName

with a static string such as

Code

Selection.Offset(0, 1).Value = "Testing"

the macro totally works like you'd think it would. So, I'm thinking it must be that the variable doesn't scope correctly somehow.

Anyway, here's the code.

Code

Public Sub AddLocation()




Dim lCount As Long
Dim rFoundCell As Range
Dim LocOffset As Range
Dim LocName As String




'Set a fake page 1 after last record for search purposes
Range("N13431").Select
Selection.End(xlDown).Select
Selection.End(xlToLeft).Select
Selection.End(xlUp).Select
Selection.Offset(1, 1).Value = "Page 1"




'Reset to top of worksheet
Range("A1").Select




Set rFoundCell = Range("B1")
For lCount = 1 To WorksheetFunction.CountIf(Columns(2), "Page 1")
    Set rFoundCell = Columns(2).Find(What:="Page 1", AFter:=rFoundCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    rFoundCell.Offset(0, -1).Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(2, 1).Select
    LocName = WorksheetFunction.Trim(ActiveCell.Value)
    Set LocOffset = ActiveCell.Offset(50, 0)
    
    With Worksheets(1).Range(ActiveCell, Cells.Find(What:="Page 1", AFter:=LocOffset, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False))
        Set c = .Find(What:="Member", AFter:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        If Not c Is Nothing Then
            FirstAddress = c.Address
            Do
                c.Activate
                c.Offset(0, 9).Select
                Selection.Value = "LOC Name"
                ActiveCell.Offset(0, -1).Select
                Range(Selection.Offset(1, 0), Selection.End(xlDown)).Select
                Selection.Offset(0, 1).Value = LocName
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress
        End If
    End With
Next lCount




End Sub

Display More