Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
GoTo macro... | Excel Discussion (Misc queries) | |||
goto function in macro 2 | Excel Discussion (Misc queries) | |||
goto function in macro | Excel Discussion (Misc queries) | |||
Using 'GoTo' in a Macro | Excel Discussion (Misc queries) | |||
Edit Macro for GOTO Part No. | Excel Programming |