Help with looping macro / goto
 

Hi to all the excel fairies,

I am using a PDF transformer to get data into excel. Unfortunately the
pdf file has tables that the transformer cannot always read properly,
therefore the data I get in excel has strange formatting errors. I was
hoping to sort all of this by code but it is simply impossible. I have
thought of another way of doing this however I am stuck with the
practicalities. (I need this data to be formatted properly before I can
run a giant macro to sort it and chart from it). I would like a
checkdata macro where the user is pointed to each "table" and asked if
the data formatting is correct. This will then make it obvious to the
user if any changes need making, (columns may need moving up or down a
cell, or data entries may need moving across a column etc) and prompt
him to make the necessary changes before continuing.

When the data is transformed it arrives in excel in the form of mini
"tables" (blocks of data normally 9 or 10 columns wide and between 1
and 30 rows down surrounded by a border). There are 26 of these
tables,on one sheet, from rows 1 down to about 900. In between each
"table" there are normally some rows of text. The top left cell of each
"table" is named car 1 to 26.

I would like to take the user to the first "table" and then ask if the
data is formatted correctly. (I have written this code off the top of
my head so apologies for rubbish code that won't work!)

For i = 1 To 26
activesheet.Range("car" & i).Select
ActiveCell.CurrentRegion.Select
msgbox("Is this data formatted correctly?"), vbyesno)

If the data is formatted correctly I want the user to click yes and
then move on to the next "table" etc etc.

That is the simple part. However if the user clicks No - (because there
are problems with the data that need fixing) I need the user to be able
to stop and fix the data. Then I need them to be able to continue
through the loop of checking the rest of the data.

Obviously if they click "No" to the msgbox I could exit the sub with a
msgbox ("Please fix the data") then ask the user to run the checkdata
macro again after making their changes, however this would mean them
re-checking the data they had already checked so is not a great way to
do it.

If anybody understands what I have just written and has any suggestions
I would be most grateful!

Thanks

Help with looping macro / goto
 

Hi Keri,

The line of code
activesheet.Range("car" & i).Select
will work only if the worksheet has Car1,Car2 etc defined as named ranges.
Otherwise you will need to replace that with a Find routine.

Also the best way to handle asking the questions is to store the answers in
another worksheet - say A1 to A26. Store a Y if the user answers a Yes. This
way when the Macro starts again you skip over those questions where Y has
already been answered.

Hope this helps.

Alok

"keri" wrote:

Hi to all the excel fairies,

I am using a PDF transformer to get data into excel. Unfortunately the
pdf file has tables that the transformer cannot always read properly,
therefore the data I get in excel has strange formatting errors. I was
hoping to sort all of this by code but it is simply impossible. I have
thought of another way of doing this however I am stuck with the
practicalities. (I need this data to be formatted properly before I can
run a giant macro to sort it and chart from it). I would like a
checkdata macro where the user is pointed to each "table" and asked if
the data formatting is correct. This will then make it obvious to the
user if any changes need making, (columns may need moving up or down a
cell, or data entries may need moving across a column etc) and prompt
him to make the necessary changes before continuing.

When the data is transformed it arrives in excel in the form of mini
"tables" (blocks of data normally 9 or 10 columns wide and between 1
and 30 rows down surrounded by a border). There are 26 of these
tables,on one sheet, from rows 1 down to about 900. In between each
"table" there are normally some rows of text. The top left cell of each
"table" is named car 1 to 26.

I would like to take the user to the first "table" and then ask if the
data is formatted correctly. (I have written this code off the top of
my head so apologies for rubbish code that won't work!)

For i = 1 To 26
activesheet.Range("car" & i).Select
ActiveCell.CurrentRegion.Select
msgbox("Is this data formatted correctly?"), vbyesno)

If the data is formatted correctly I want the user to click yes and
then move on to the next "table" etc etc.

That is the simple part. However if the user clicks No - (because there
are problems with the data that need fixing) I need the user to be able
to stop and fix the data. Then I need them to be able to continue
through the loop of checking the rest of the data.

Obviously if they click "No" to the msgbox I could exit the sub with a
msgbox ("Please fix the data") then ask the user to run the checkdata
macro again after making their changes, however this would mean them
re-checking the data they had already checked so is not a great way to
do it.

If anybody understands what I have just written and has any suggestions
I would be most grateful!

Thanks

Help with looping macro / goto
 

Alok thanks.
Yes they are named ranges.

I didn't know i could store values in another sheet. Your idea is
excellent. I have a few further questions.

1. How would I write the code that makes it search for the Yes in
another sheet, and if there is a Yes to skip this range?

2. Also where I was going to select the named range this doesn't
automatically make it the visible range on the screen - therefore
defeating the object of the user being able to answer the question in
the box as he will have to scroll down to find the range himself. How
can I make this visible?

3. Further to this, if a user selects No from the box and then corrects
the data he will then have to scroll back to the top of the screen to
press the button again to resume the macro. Can I create a floating
button that is always visible?

Help with looping macro / goto
 

Thanks so much for helping. I now have this code;

For i = 1 To 26
Application.Goto Sheets("raw2").Range("car" & i), True
response = MsgBox("Is CAR" & "" & i & "data correct?", vbYesNo)
If response = vbNo Then
Worksheets("data").cells(i, 1).Value = "N"
MsgBox ("Please correct the data then resume checking")
Exit Sub

Else:
Worksheets("data").cells(i, 1).Value = "Y"
End If
Next i

The code works and places the Y and N values into column A on the data
sheet. I then inserted this code below the For i = 1 to 26;

If Sheets("data").Range("A" & i) = Y Then
End If
Next i
Else:

However this gives me errors. Is this the correct way to check the Y &
N values on the data sheet or am i doing something stupidly wrong?

Help with looping macro / goto
 

Thankyou so much for your help. This has taught me a lot and helped me
to come nearer to completion of this sheet. I really am grateful to
everyone on here.

Happy Christmas and thanks again.