Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test & replace value
I am learning VBA for Excel (v2000) under pressure. We have large
spreadsheets (at least by my standards, 10,000 to 32,000 rows) in which I need to find existing flags and add flags above and below the existing flags based on 2 criteria which exist in the same row as the value I'm testing and changing. (I'm slightly simplifying the scenario because I think if I can get help with this part, I can do the rest.) In these spreadsheets, Col A contains an ID value (alphanumeric), Col B I don't care about, Col C is a flag, 0 or 1, Col D is an alpha field, Col E is a date. I've given each column a range name thinking that might make the macro easier to write. The solution can use the range names or not. The macro should look for the value "1" in column C (named range "rangeC"), then save the Row number as iBaseRow. Then I want to get the corresponding value in Column A (named range "rangeA") and saves it as "strBaseSID") and the date in column E as "iBaseDate". Then I want it to go up one row and test to see if the value in column A is the same as strBaseSID. If yes, then move on to compare the date from the same row to see if it's equal to or greater than iBaseDate -90. If the ID's or dates don't match, continue the macro one row below the BaseRow. If both criteria are met, the flag in Col C should be set to 1. Then it should move up one row and repeat the process. I hope this explanation is clear. Can anyone help me with this? TIA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test & replace value
Dave
Your explanation is not very clear. You start out checking for the flag 1 in Col C then do all the checks and if they are valid set the flag to 1 which is what it was to start with. You also say if the criteria are not met move down and continue whereas if the criteria are not met move up and continue. This will result in the macro going up and down and potentially never ending. Try posting some of your data eg: ID Flag Date 1 0 25/10/05 2 1 15/10/05 and then explain again. Regards Rowan "davegb" wrote: I am learning VBA for Excel (v2000) under pressure. We have large spreadsheets (at least by my standards, 10,000 to 32,000 rows) in which I need to find existing flags and add flags above and below the existing flags based on 2 criteria which exist in the same row as the value I'm testing and changing. (I'm slightly simplifying the scenario because I think if I can get help with this part, I can do the rest.) In these spreadsheets, Col A contains an ID value (alphanumeric), Col B I don't care about, Col C is a flag, 0 or 1, Col D is an alpha field, Col E is a date. I've given each column a range name thinking that might make the macro easier to write. The solution can use the range names or not. The macro should look for the value "1" in column C (named range "rangeC"), then save the Row number as iBaseRow. Then I want to get the corresponding value in Column A (named range "rangeA") and saves it as "strBaseSID") and the date in column E as "iBaseDate". Then I want it to go up one row and test to see if the value in column A is the same as strBaseSID. If yes, then move on to compare the date from the same row to see if it's equal to or greater than iBaseDate -90. If the ID's or dates don't match, continue the macro one row below the BaseRow. If both criteria are met, the flag in Col C should be set to 1. Then it should move up one row and repeat the process. I hope this explanation is clear. Can anyone help me with this? TIA |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test & replace value
Rowan,
Thanks for your reply. Sorry if my explanation was confusing. The sheet looks something like this: ID Flag Date G4 0 02/25/04 H6 1 09/07/03 Actually, there are additional fields between these with text and numbers not relevant to the macro. I want the macro to search first in the Flag field until it finds a "1". I want it to store the ID (iBaseSID) and date (iBaseDate), and calculate the date 90 days earlier (iEarlyDate). I then want it to back up one line and compare ID's to see if they are the same. If they are, I want it to check and see if the CurDate (the date on the current line) is greater than the EarlyDate (BaseDate-90). If both conditions are met, I want it to change the current Flag to 1 (It won't be 1 because it was tested before and didn't = 1, other wise the macro would be start testing the other variables on that line). I want it to contunue moving up one line at at time, changing the flag until one of the conditions is not met, then go to the line below the Base line and repeat the process going down, except that the date it will be testing for is iLateDate (iBaseDate + 90) and it will test for less than or equal to. (Actually, there is a third test that will be run, but I figured that if I got the code to get me this far, I can figure out how to add one more test loop.) I omitted to talk about how it gets out of the loop because it's very simple and I already have figured out how to do it. The first test in the final version of this will be for a blank cell. When it finds a blank cell, the macro ends. The only blank cells are below the data. Sorry if omitting this cause confusion, I thought it would unneccessarily complicate my explanation and the reply. TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can you help test our Excel test? | Excel Discussion (Misc queries) | |||
Calculate mean of test scores from rows of test answers | Excel Discussion (Misc queries) | |||
find and replace - replace data in rows to separated by commas | Excel Worksheet Functions | |||
loosing test fomat after using replace command | Excel Worksheet Functions | |||
test..where are my messages..test | New Users to Excel |