If else, If then statement needed when data doesnt exist ... help

[Hondahawkrider]

Situation... I have inheirited a fairly involved excel macro and am running into a problem... THis macro imports a number of files as seperate worksheets, then reorganizes the data in the worksheets and copies it to a central sheet and does some calucations... THe problem is that the macro has a section for editing each workstheet... THere is a sample below... Basically, the script runs "Batch1_GETS, Batch2_GETS, and so on till Batch8_GETS, This edits the data on th worksheet and then copies data to another worksheet after the fact (ParaSoft_Data) for additional calculations... IT woks fine, but the problem is that now the number of batches has now started varying. Now alot of the time we only get Batch 1-6, but we still on occation get 8, so I don't want to remove that section of the code in the macro... The problem is that since there is no Batch7/8 worksheet that when it gets to the Batch7_GETS section in the macro, it causes problems.

Normally, i would a IF NOT EXIST/ GOTO statement... Basically, I would note in the code - that if something didn't exist (ie Batch7/8_GETS worksheets) it would just skip that section and go on... I know that excel has IF/ElSE statements - but I can't get it work... So I figured I would ask... I would think it shouldn't be too difficult, so if anyone if familiar, I would appreciate some help....

Ultimately, I would prefer to loop the BatchX_GETS statements, so there wasn't a seperate piece for each workbook, but one thing at a time...

Thansks in advance..

Please Login or Register  to view this content.

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

[Mumps1]

Could you post a copy of your file with the complete macro included?

[Hondahawkrider]

Here you go...

The Macro goes sequentially Batch1-8_GETS... Each step is basically the same ... It deletes and reorderes each BatchX_GETS worksheet, and copies data to a centralized sheet (Parasoft Data). The Code for each step is basically the same with one exception - it copies the data to a different collumn in the centralized sheet (A3 for Batch1, F3 for Batch2, and so on)...

The sheet works fine if I have a total of 8 batches ... THe problem is that I now doesn't always wind up with batches 1-8, but often get less usually 6 ... THis ultimately means that that a Batch7_GETS and Batch8_GETS worksheets aren't creiated ie doesn't exist - so when my script gets to that point and doesn't find those sheets it causes problems...

I am looking for a way for teh marco to do a check of whether or not a worksheet exists, if it doesn't to skipt that setp and go on to the end, or next batch (sometimes I get a missing batch in the middle)...

Please Login or Register  to view this content.

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

[AlphaFrog]

Try this...

Please Login or Register  to view this content.

[Hondahawkrider]

didn't work...

Debugger highlights

.Columns("A:B").Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

I don't actually think it's an issue - as not columns are populated ..

I have tried the loop but I wasn't able to get it to work...

I do have one question concering this line

If Evaluate("ISREF('" & strTabName & "'!A1)") Then 'Test if worksheet name exists

what's the A1 for ...

[AlphaFrog]

It should be B1

Please Login or Register  to view this content.

If Evaluate("ISREF('" & strTabName & "'!A1)") Then 'Test if worksheet name exists

what's the A1 for ...

The ISREF worksheet function requires cell reference to test if it is a valid reference. It could be any cell as it's purpose is to test the worksheet name.

[Hondahawkrider]

still didn't work... still 1004 error...

Debug hightlights the same string...

I thru in the
"On Error Resume Next" to see what happened....

results are odd....

The Batch1_GETS thru Batch4_GETS Tabs (worksheets) have deleted the C:L columns as it was supposed to do - but the data wasn't sorted prior (b2:B135) prior to the deletion - so the data isn't correct...

However, Batch6_GETS deleted the C:L columns - sorted the data correctly and then did the delete properly... So the correct data is in that tab (it's the only tab that's right).

THe data in all the tabs was copied correctly to the Parasoft Data worksheet (however as noted tabs BATCH_GETS1-5 are incorrect - but BATCH6_GETS6 is fine...

dontgetit

[AlphaFrog]

I missed adding a period before Range. Sorry.

.Columns("A:B").Sort Key1:=.Range("B1"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

[Hondahawkrider]

No worries... It worked great... thanks much