Posts by jpmclaughlin

    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.

    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

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

    with a static string such as

    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.