Free Microsoft Excel 2013 Quick Reference

Making the data to go next line within cell Results

hii

I have attached an excel sheet for a clear demonstration of my problem.

Under the columns Accounts Debited,Accounts Credited,Amounts Debited and Amounts Credited few account numbers have been listed out.

All the Non-Standard Entries belong to one set of range and all Standard Entries belong to another set of range.

My problem here is for each cell under the above mentioned columns

for example;
In the 16 th row they are two account numbers under c column.

"123-456789+164-123456"

So these account numbers are coming in one line though they are two account numbers.
Each Account number is prefixed by a '+' sign.

I want it such a way that right after entering the first account number the second account number should come into the next line in THE SAME CELL rather than manually pressing enter.

I need a macro which works individually for each CELL under and for all four columns.

Demonstration :

This is the input:-(example 19th row,c column)

"111-123456+111-345678+111-112233+111-334455+111-456567+111-234567+121-765890+231-123456"

The output should come in this way:-

111-123456(first account number doesnt have "+" sign)
+111-345678(it should come in the next line rathar than in the same line in that particular cell dynamically along with the + symbol)
+111-112233
+111-334455
+111-456567
+111-234567
+121-765890
+231-123456

This is for ONE cell.The First account number doesnt have "+" sign. Only "+" starts with the second account number.

1.After it experiences with "+" symbol the next account number should come in the next line along with the "+" sign rathar than in the same line for both standard and non standard enteries under the 4 columns for each cells.

2.I need it in the form of a macro.

The input is given in the insert function box which is nothing but the f suffix x box.
The output should come is the cells likely.

Thank You.

Here is the solution, nothing was populated in column I so the data kept resetting the row to update as 2. I changed the column to where updates were always present and it worked like a charm. Thank you for your assistance. I am attaching the working code incase anyone needs it.

Private Sub CommandButton6_Click()

Worksheets("Proposals").Range("A3:S65536").ClearContents

'---------This Section populates the 'Proposals' tab
Set rd = Sheets("Presentations") 'set read data sheet as rd
Set wd = Sheets("Proposals") 'set write data sheet as wd

    For i = 103 To rd.Range("S65536").End(xlUp).Row ' set i to the last row in column S
   
         If LCase(rd.Cells(i, 10).Value) Like "*proposal*" Then
          wd.Cells(wd.Range("C65536").End(xlUp).Row + 1, 3) = rd.Cells(i, 10)
          wd.Cells(wd.Range("C65536").End(xlUp).Row, 1) = rd.Cells(i, 4)
          wd.Cells(wd.Range("C65536").End(xlUp).Row, 2) = rd.Cells(i, 6)
          wd.Cells(wd.Range("C65536").End(xlUp).Row, 4) = rd.Cells(i, 11)
          wd.Cells(wd.Range("C65536").End(xlUp).Row, 5) = rd.Cells(i, 15)
          wd.Cells(wd.Range("C65536").End(xlUp).Row, 6) = rd.Cells(i, 16)
          wd.Cells(wd.Range("C65536").End(xlUp).Row, 7) = rd.Cells(i, 17)
          wd.Cells(wd.Range("C65536").End(xlUp).Row, 8) = rd.Cells(i, 18)
          End If
     
     Next i
Hello Everybody,

Hope all is well. I have written the following code. It takes data from one worksheet and imputs it into another. What this is suppose to do is take the data (Rows 103 to 65536) from the Presentations worksheet and put them into a smaller spreadsheet starting on row 3. For each successful if statement, the return should be on its own row.

I have this so it searches for the first empty row then inputs the data on the proposals workbook. The problem I am running into is that the first line is that this data:

is placing the data in the correct spot (Row 3), but all subsequent sucessful if statements are re writing on the same line. 
How and where do I add one to the row.  

***This is a sample of one workbook. I am pulling off of 5 worksheets within the same excel file. The code all the same repeated 5 times. So when one is done then I will need the row to keep going for the next set of information. I feel that I can add this easy enough, I just want to identify it in case there is an issue you may forsee.

Here is the repeated code.

Private Sub CommandButton6_Click()

Worksheets("Proposals").Range("A3:S65536").ClearContents
TheDate = Date

'---------This Section populates the 'Proposals' tab
Set rd = Sheets("Presentations") 'set read data sheet as rd
Set wd = Sheets("Proposals") 'set write data sheet as wd
     
    For i = 103 To rd.Range("S65536").End(xlUp).Row ' set i to the last row in column S
        If rd.Cells(i, 10).Value Like "*proposal*" Then
          wd.Cells(wd.Range("I65536").End(xlUp).Row + 1, 3) = rd.Cells(i, 10)
          wd.Cells(wd.Range("I65536").End(xlUp).Row + 1, 1) = rd.Cells(i, 4)
          wd.Cells(wd.Range("I65536").End(xlUp).Row + 1, 2) = rd.Cells(i, 6)
          wd.Cells(wd.Range("I65536").End(xlUp).Row + 1, 4) = rd.Cells(i, 11)
          wd.Cells(wd.Range("I65536").End(xlUp).Row + 1, 5) = rd.Cells(i, 15)
          wd.Cells(wd.Range("I65536").End(xlUp).Row + 1, 6) = rd.Cells(i, 16)
          wd.Cells(wd.Range("I65536").End(xlUp).Row + 1, 7) = rd.Cells(i, 17)
          wd.Cells(wd.Range("I65536").End(xlUp).Row + 1, 8) = rd.Cells(i, 18)
        End If
    Next i
Any and all help is appreciated. I would like to send a thank you out to all that have been so gracious and kind with their time already, helping me learn my way around VBA.

Regards,
jsgray

I had d 1-D array which I was loading with cell addresses
within a For Loop. I was redimensionin the array based
on a counter witin my loop since I don't know how big to
make it. It was working fine. Then I decided to
introduce a second dimension. All I wanted to do was
introduce a new column in the array which I was not yet
filling. That is, I still want to my data to go into
column 0. I can't seem to load the array with data. I
have skipped out how I progress though the cells as this
is extensive code. But I have included the main lines.
I have done something wrong in the new version. Can
anyone tell me what I have done wrong? Thanks

Old:
UniqueCount = 0
ReDim UniqueCellAddressArray(0 To 0)
For X = 1 to 100
UniqueCount = UniqueCount + 1
ReDim Preserve UniqueCellAddressArray(0 To UniqueCount)
UniqueCellAddressArray(UniqueCount, 0) =
CurrentCell1.Parent.Name & "!" & CurrentCell1.Address
Debug.Print UniqueCellAddressArray(UniqueCount)
Next

New:
UniqueCount = 0
ReDim UniqueCellAddressArray(0 To 0, 0 To 0)
UniqueCount = UniqueCount + 1
ReDim Preserve UniqueCellAddressArray(0 To UniqueCount, 0
To 1)
UniqueCellAddressArray(UniqueCount, 0) =
CurrentCell1.Parent.Name & "!" & CurrentCell1.Address
Debug.Print UniqueCellAddressArray(UniqueCount, 0)

Within a macro I already created, I need to edit it to reference and rename the worksheet created based on a cell in the same worksheet. The newly inserted sheet is placed in the 5th postion as Sheet1 (i.e. Worksheets(5))

I created a macro to reference data in multiple sheets before and after this new sheet (i.e. Sheet1). However, I may need to run this macro multiple times within every session using this workbook, but I can't because the macro is going to have a reference to "Sheet1" everytime thought the next sheet will be Sheet2, Sheet3, etc.

So, when I rename the sheet I have to keep in mind within the macro, there are a few times it makes reference to Range("='Sheet1'!$F$3:$J$4")

What I need to do is rename the sheet when it is created and make sure the existing references to Range("='Sheet1'!$F$3:$J$4") are edited properly.

How would I code this? My macro is all one Sub > End Sub (1000 lines of code) and I'm a newbie. Below is how the macro starts and how it ends:

Beginning:


	VB:
	
 InsertParticularsSheet() 
     '
     ' InsertParticularsSheet Macro
     ' Inserts and Linkes Particulars Sheet
     '
     ' Keyboard Shortcut: Ctrl+p
     '
     ' Created the Titles of the First Particulars
     ' Renamed Worksheet to Current Date
     
    Sheets.Add Before:=Worksheets(5) 
     
     
    ActiveWindow.Zoom = 70 
    Columns("A:A").Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Ending:Refers to the sheet itself then renames it at the end, but I need it to happen before.


	VB:
	
 '
Range("$f$3:$j$4").Select 
ActiveSheet.Shapes.AddChart.Select 
ActiveChart.SetSourceData Source:=Range("='Sheet1'!$F$3:$J$4") 
ActiveChart.ChartType = xlLine 
ActiveChart.Legend.Select 
Selection.Delete 
ActiveSheet.ChartObjects("Chart 1").Activate 
ActiveChart.Axes(xlValue).MajorGridlines.Select 
Selection.Delete 
ActiveSheet.ChartObjects("Chart 1").Activate 
ActiveChart.Axes(xlCategory).Select 
ActiveSheet.ChartObjects("Chart 1").Activate 
ActiveSheet.ChartObjects("Chart 1").Activate 
ActiveChart.SeriesCollection(1).Select 
ActiveSheet.ChartObjects("Chart 1").Activate 
ActiveChart.SeriesCollection(1).ApplyDataLabels 
ActiveSheet.ChartObjects("Chart 1").Activate 
ActiveChart.SeriesCollection(1).DataLabels.Select 
Selection.Position = xlLabelPositionAbove 
ActiveSheet.ChartObjects("Chart 1").Activate 
 
 ' Rename the New Sheet
 '
Worksheets(5).Select 
Worksheets(5).Name = "Rename" 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi - I am new, here and at VBA, and this is my first post. I have searched the site many times in the past two months and have found plenty of very helpful answers, and have learned quite a bit about how to work with code, so thank you for that! I cannot find anything that helps me work out how to do the following, however, and hope someone can help.

I have managed to get this far - I have put together a macro (from different threads on this site) that opens closed workbooks, copies data in one of the sheets (same sheet in each of 28 books), and pastes the data it into a master book sheet, each paste starting below the last. So that bit is working. The first bit of help I need is a line of code that will make the macro loop through a number of sub folders in a main folder. My code at the moment works as long as I specify a path that ends with the name of one subfolder, and it only loops through this subfolder. I would like the path to end at the folder that holds all the subfolders ('Workbooks' in the path below), and then add some code that tells it to apply the macro to all subfolders in this folder, so it loops through them all.

The second issue is that after the macro goes to the closed book(s), copies the data in there and pastes it into the master sheet (into columns E:FG), I then need it to go back to the workbook it just copied from, go the same sheet, to three specific cells on that sheet (FH1:FH3), copy the content, go back to the master sheet, and now repeatedly paste the content (values only and transposed) of these three cells into three cells (in columns B:D, with row number being dependent on what rows the first lot of data was copied into) next to every row it just previously pasted in for me. When it loops to the next workbook, it needs to do the same, and the three cells will have different content than the ones in the previous workbook paste.

I dont know how to define the range it needs to paste into the second time. I tried using the definition I used for the first paste (MCDrow), to tell it that it is the same rows, just different columns, but this is not working.

Here is what I have so far, which does the first part of what I need, except for needing a way to have it loop through all subfolder in the 'Workbooks' folder (at the moment it lists Barwon South West as a subfolder in that path, but I actually have multiple subfolders, not all called Barwon South (all different names) that it needs to loop through and do both the first and the second paste for. I have taken out the code I was trying to use to do the second paste, as this was not working and the code is pretty messy as it is (I sort of bumble along, being so new, and I know the code is not very clean or efficient!).

Can someone help me put in the few lines I need to loop through all my subfolders (if you give me an example I can probably extrapolate), but to get you started, three of the subfolders are Barwon South West, Eastern Region and Gippsland. And can someone help me put in the code that will do the second paste for each workbook?

Thank you so much! My hours of searching and trial and error are not working for me on this one and I am really stuck!

Here is what I have so far and sorry for the long post (I wanted to be be as clear as possible!):


	VB:
	
 Click2() 
     
     
    Application.ScreenUpdating = True 
    Dim MCDrow As Long 
     'Dim SubFolders As String
    MCDrow = ThisWorkbook.Sheets("Client Data").Range("A65536").End(xlUp).Row 
     
    Fpath = "Q:Clinical ServicesCS Statewide DatabaseWorkbooksBarwon South West" ' change to your directory
     'SubFolders = True
    Fname = Dir(Fpath & "*.xls") 
    Do While Fname  "" 
         
        ThisWorkbook.Sheets("Client Data").Unprotect 
         
        Workbooks.Open Fpath & Fname 
        Worksheets("Client Data").Activate 
        Worksheets("Client Data").Unprotect 
         
         
         
        Worksheets("Client Data").Activate 
        Range("B4:FG4", Selection.End(xlDown)).Select ''This assumes header in first row''
        Range(Selection, Selection.End(xlToRight)).Select 
        Selection.Copy Destination:=ThisWorkbook.Sheets("Client Data").Cells(MCDrow + 1, 5) 
         'need something here that does the second paste before it loopto the next book
         
        Workbooks(Fname).Close SaveChanges:=False 'or Fname
        Fname = Dir 
         
         ''' Lets get the last row for the next copy/paste '''
         
         
        MCDrow = ThisWorkbook.Sheets("Client Data").Range("E65536").End(xlUp).Row 
         
    Loop 
    Application.ScreenUpdating = True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


So I have this massive text file, for around 1,000,000 records. There are no delimiters between the records, or their elements. All I have is a layout file that tells me how many characters each element is within each records. Every record has all the same elements, so each total record is the same length.

The first thing I tried was importing the massive mess into Excel. This split each row into about 150 records. The problem was it would drop one character in between each row. Presently my best effort would be to mark each cell that is likely missing a character, and go back through and correct ~3,100 cells that would have incorrect data. Tedious at best. Plus all the testing I need to do to make sure I don't have to jump down to the next row makes each record take about 1 seconds to parse, which I believe means it would take 11 days to run through on the first pass.

So next I tried to just open the file into memory, but the problem with this is when I try to Line Input into a string, it tries to jam all 100MB's into one string. Obviously this overflows.

So my question is, does anyone know how to "Line Input" but only grab a portion of a line from a txt file? If I could do that, then I could just grab one record at a time and work with it quickly.

Otherwise if anyone has any great ideas, I'm totally open. Thanks!

Hi All,

I have the following code (see end of message) that should select only a certain number of worksheets from the original file and then save the selected files as a new work book.

The reason I want to do this is because I would like to share the workbook with other people, however not all of the workbook because the original file has sensitive data in it. The idea is that by pressing a button a predefined selection of worksheets are copied and then saved in a separate workbook.

This is currently half working.

Basically at the moment if I comment out the line:
'ws.Cells.PasteSpecial Paste:=xlValues

The process works fine. The problem however is that the copied workbook contains links to the original file. Consequently if I e-mail this sheet my values appear as broken links (i.e. ####).

I really need to get the Past Values option to work – any help would be much appreciated.

Regards,

Kevin

Sub Button9_Click()

Dim NewName As String
Dim nm As Name
Dim ws As Worksheet

If MsgBox("Copy the 'Quarterly Report' sheet to a new file to e-mail to DAAT" _
, vbYesNo, "NewCopy") = vbNo Then Exit Sub

With Application
.ScreenUpdating = False

' Copy specific sheets
' *SET THE SHEET NAMES TO COPY BELOW*
' Array("Sheet Name", "Another sheet name", "And Another"))
' Sheet names go inside quotes, seperated by commas

On Error GoTo ErrCatcher
Sheets(Array("i. Guidance", "ii. Contents", "1. Perf", "2. InfoNeeds", "3. ServDevel", "4. StaffDevel", "6. Incidents", "7. Partnership", "8. Finance")).Copy
'Modules(Array("Module1", "Module2")).Copy
On Error GoTo 0

' Paste sheets as values
' Remove External Links, Hperlinks and hard-code formulas
' Make sure A1 is selected on all sheets

For Each ws In ActiveWorkbook.Worksheets

ws.Cells.Copy
'ws.Cells.PasteSpecial Paste:=xlValues
ws.Cells.Hyperlinks.Delete
Application.CutCopyMode = False
Cells(1, 1).Select
ws.Activate
Next ws
Cells(1, 1).Select

' Remove named ranges
For Each nm In ActiveWorkbook.Names
nm.Delete
Next nm

' Input box to name new file
NewName = InputBox("Please Specify the name of your new workbook", "New Copy")

' Save it with the NewName and in the same directory as original
ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "" & NewName & ".xls"
ActiveWorkbook.Close SaveChanges:=False

.ScreenUpdating = True

MsgBox ThisWorkbook.Path & "" & NewName & ".xls saved."
End With

Exit Sub

ErrCatcher:

MsgBox "Specified sheets do not exist within this workbook"

End Sub

Von Pookie's HTML Maker FAQ
Last updated: 07 July 2009

07 July 2009: Downloads are currently unavailable for Colo's site. If you would be interested in testing the new HTML Maker designed specifically for the MrExcel forum, please see this thread:
http://www.mrexcel.com/forum/showthread.php?t=399380

PLEASE NOTE: This FAQ was written while we were using phpbb forum software. I have not checked these instructions for accuracy with the new vBulletin software we are currently using. It is possible that some items listed here may no longer apply.

Please note the following:
This is not the FAQ for the VBHTML Maker nor the YaBB Table Maker.These directions were written using Windows 2000 and XP, and Excel 2002 and 2003. I do not (currently) know the information for Macs or any other versions of Windows or Excel.

How can I show a small image of my worksheet in my post?
You can simply download the handy HTML Maker add-in! The HTML Maker is available for download at Colo's site. Currently unavailable

I have downloaded the .zip file. What is the next step?
You may need to download an extraction program such as WinZip to open the .zip file and extract the add-in file. For now, just extract the HtmlMaker.xla file to your desktop. Once you have this, you no longer need the .zip file.

Next, you need to locate the Addins folder on your computer. There are several places you may see a folder with this name. From the Excel help file: "Add-ins are stored by default in one of the following places:
The Library folder or one of its subfolders in the Microsoft OfficeOffice folder.The Documents and Settingsuser nameApplication DataMicrosoftAddIns folder."If you're uncertain which folder to use, open Excel, go to the Tools menu and select Add-ins. In the dialog that will display, click the Browse button to see the path for the AddIns folder it is using. Once you've located this, you can simply drag and drop the HtmlMaker.xla file into it.

Note: If you cannot find the Application Data folder, you probably just need to change a setting on your computer.Open Windows Explorer (or any folder) and select Folder Options from the Tools menu.On the View tab, look for "Hidden files and folders" under the Advanced SettingsMake sure the option "Show hidden files and folders" is selected, click OK, and look for that pesky Application Data folder again.

I have added the file to the AddIns folder, but I am unable to activate it in Excel!
Start Excel, go to the Tools menu and select Add-Ins. You should see a dialog box with a list of available add-ins, with a checkbox next to each item. Simply find the one labeled Htmlmaker, check the box next to it and click OK. You should now have a new menu between the Window and Help menus called HTML. You have now installed and activated the HTML Maker, and should be ready to go!

How do I use this add-in?
Using the HTML Maker to post a "snapshot" of your worksheet on the message board is relatively simple.
[list=1][*]Make sure you have enabled HTML in your board profile.Click the "profile" link--which is in the group of text links at the top-right of every page of the board.In the list of preferences, make sure the option for "Always allow HTML" is set to Yes.Click the "Submit" button to save any changes to your profile[*]Make sure the HTML Maker add-in is active (see "I've added the file to the AddIns folder, but I can't figure out how to activate it in Excel!").[*]Go to the HTML menu and select "Convert sheet to HTML"[*]Select the range you want to copy
Please select only the range (preferably with little to no formatting) that is crucial to the question you are asking.[*]Click OK[*]On the message box that pops up, click "yes" (I honestly have no clue what this does, but I always click yes and it works just fine)[*]Another message box will appear, letting you know how many characters the HTML Maker code will use, and how many characters that are remaining for you to use in typing the rest of your message. Click OK.[*]A new browser window will open, displaying what your copied cells will look like when posted to the board.[*]In this new window, click the "Copy code to clipboard" button.
If this does not work...[list=a][*]If you are running Windows XP with Service Pack 2 installed, make sure you allow popups on the page (they will be blocked by default) and try clicking the button again[*]As an alternative:In this same window click the button that is on the page that is labeled "View Source." This is located right next to the "Copy to Clipboard" button.Copy the entire code that is displayed there.[/list:o:4e41f3da2f][*]Come back to the board where you are entering your message, and put your cursor in the area where you type the body of your message.[*]Press Ctrl+V in the area where you type. If you don't know HTML, it will look like a lot of gibberish. That's normal, don't worry about it. You can still edit and add to your message if needed at this point. Do not edit/add to the code from the HTML Maker.[*]When you are finished, click the Submit button to post your message. The table should appear in your post just as it did on the browser page you copied the code from.
Do not preview a message containing HTML Maker code. You will not be able to use the buttons on the preview page to submit your message.Do not quote a post using HTML Maker code. You will receive errors (when quoting a message, simply edit out any extra HTML Maker code).[/list:o:4e41f3da2f]Back to top

Known Issues & Errors

I installed the HTML Maker add-in, but the "HTML" menu you say should appear on the menu bar is not there.
It is possible that you have installed the wrong add-in. This FAQ is for Colo's HTML Maker, NOT the VB HTML Maker or YaBB Table Maker. Making sure you have installed the correct add-in is the first step.

If you have confirmed that you installed Colo's HTML Maker correctly, make sure you have enabled the add-in. Go to ToolsAdd-Ins, click the checkbox for the HTML Maker and click OK. The menu should then appear.

If you have performed both of the previous steps and the menu is still not appearing, post your questions in the Questions About This Board forum.

I followed the directions, but after posting the page is not displaying correctly.
You can either edit the post yourself and remove ALL of the HTML code, or report the post by clicking the "Report" button () on the post to notify the moderators of the problem. A moderator will edit the post to remove any/all HTML Maker code so the thread will be able to display correctly.

As to why the page displayed strangely in the first place, it's usually one of two things: either you didn't follow the directions and copied the source of the page the HTML Maker generated instead of using the "view source" button on the page (yes, there is a difference and we can tell from the code if this is the case), or you have inadvertently posted incomplete HTML Maker code--which is much more common.

HTML requires both opening and closing tags; if you do not post the entire code, the closing tags are not included and the board cannot interpret the code correctly.

When using an HTML Maker example in your post make sure that you have copied all of the generated code and that it is pasted into the message in its entirety. There is also a character limit for each message (but to be completely honest, I have no idea what that limit actually *is*). If you try to use multiple examples within a single post, you can hit the limit quickly and the end of the code could be cutoff.

Error: "Could not load an object because it is not available on this machine"
You may be missing a file called ietimer.ocx, which is required. Click here to go to Colo's site and check if the file is already installed on your computer. This link will also automatically install the file for you if it is needed.
The file is also available for download from Microsoft: http://activex.microsoft.com/control...86/ietimer.cab After downloading the file, it must be registered with regsvr32. The easiest way to do this is to right-click the file and select Install from the context menu.

Error: "Compile error." or "Can't find project or library..."
Open the Visual Basic Editor by pressing Alt+F11. Once in the editor, select the HTMLMaker in the Project window then go to the Tools menu and select References.
If there are any references that start with "MISSING," uncheck them and don't forget to save.
More information on this error can be found in this thread: http://www.mrexcel.com/board2/viewtopic.php?t=107231

Error: "Error Number: 91, Object Variable or with Block variable not set..."
Click OK to close the dialog box with the error and go to Tools->Macros->Security. Click the Trusted Sources tab and check the box next to "trust access to Visual Basic Project," and click OK.

In Excel 2007, the directions are slightly different:
Click the Office Button and go to Excel Options->Trust Center->Trust Center Settings->Macro Settings->Developer Macro Settings. Then you can Check the "Trust access to the VBA project object model" box and click OK.
(Thanks to Bradlee_S for posting this, because I would have never thought to add it otherwise)

Error: "Runtime error: 5 Invalid procedure call or argument."
When this error occurs, the following line will be highlighted in the debugger:
Code:
Open the Visual Basic Editor (Alt+F11) and go to Tools->Options. Select the General tab and make sure "Break on unhandled
errors" is selected for "Error Trapping."

First mention of this error (and solution) can be found in this thread:
http://www.mrexcel.com/board2/viewtopic.php?t=242967

I am unable to view formulas after clicking the formula link in the posted example
There are two or more posts in the thread with HTML Maker examples, which could cause a conflict in the Javascript code being used. There is no real "fix" for this issue that I know of. The only real workarounds are to not post a message using the HTML Maker code in a thread already using an example elsewhere, or if you absolutely need to show how the data is set up, use the HTML Maker to show your sheet and indicate what formulas are in what cells within your message. Users viewing the examples in Firefox may notice this problem as well. (I have this issue with Firefox no matter what, personally).

I am unable to copy the generated code to the clipboard.
As was discovered by board member Parry: "If you have installed Windows XP Service Pack 2, a Pop-Up blocker is installed [in Internet Explorer] as part of the pack and by default this will block active content.

When you select your cells and run HTML Maker it will create an HTML page of your image as expected, but the pop-up blocker will prevent you copying the code when you click the button to send Source to the clipboard. Simply click on the pop-up security information bar, then select Allow Blocked Content and you can now copy the HTML to the clipboard."

Back to top

Miscellaneous
[list][*]This add-in is simply to show a small "snapshot" of the selected area of your spreadsheet. However, some things will not display when posting an HTML Maker example, such as:Drawings and objects (i.e., items created from the Drawing toolbar)Clip art and other imagesCharts and graphsConditional formatting[/list:o:4e41f3da2f]Questions concerning the HTML Maker should be posted in the Questions About This Board forum.
If you have not used the HTML Maker add-in before, please post in the Test forum before making your actual post to make sure the example will display correctly. If any posts with HTML maker code end up "breaking" the page (including posts in the Testing forum), they will be edited by a moderator or administrator to remove the problem-causing HTML code from your post or the post itself will be deleted.

Find an error or something I missed?
Click to send me a private message

Hey everyone,

The lowdown: the formatting code (starting at "With" and ending at the "ActiveSheet" line) within this loop works perfectly when not run in a loop. When run in a loop, however, Excel looks like it is going through all of the individual tabs setting formats, but when it is done, no changes have been made. In terms of what my data looks like, I use this format program on just about every sheet I make, regardless of data size/complexity/layout. Any thoughts on why?


	VB:
	
 LoopThroughSheets() 
    Dim ws As Worksheet 
    For Each ws In ActiveWorkbook.Worksheets 
         
         '** Perform code here **
         
         
        With ActiveSheet.PageSetup 
            .Zoom = False 
            .FitToPagesWide = 1 
            .FitToPagesTall = False 
            .PrintTitleRows = "$1:$2" 
            .PrintTitleColumns = "" 
            .PrintArea = ActiveSheet.UsedRange.Address 
            .LeftHeader = "&""Times New Roman,Regular""Preliminary Draft" 
            .CenterHeader = "" 
            .RightHeader = "&""Times New Roman,Regular""Privileged and Confidential" & Chr(10) & "Prepared for Use of
Counsel" 
            .LeftFooter = "" 
            .CenterFooter = "" 
            .RightFooter = "" 
            .LeftMargin = Application.InchesToPoints(0.75) 
            .RightMargin = Application.InchesToPoints(0.75) 
            .TopMargin = Application.InchesToPoints(1) 
            .BottomMargin = Application.InchesToPoints(1) 
            .HeaderMargin = Application.InchesToPoints(0.5) 
            .FooterMargin = Application.InchesToPoints(0.5) 
            .PrintHeadings = False 
            .PrintGridlines = False 
            .PrintComments = xlPrintNoComments 
            .PrintQuality = 600 
            .CenterHorizontally = True 
            .CenterVertically = True 
            .Orientation = xlLandscape 
            .Draft = False 
            .PaperSize = xlPaperLetter 
            .FirstPageNumber = xlAutomatic 
            .Order = xlDownThenOver 
            .BlackAndWhite = False 
            .PrintErrors = xlPrintErrorsDisplayed 
            .OddAndEvenPagesHeaderFooter = False 
            .DifferentFirstPageHeaderFooter = False 
            .ScaleWithDocHeaderFooter = True 
            .AlignMarginsHeaderFooter = True 
            .EvenPage.LeftHeader.Text = "" 
            .EvenPage.CenterHeader.Text = "" 
            .EvenPage.RightHeader.Text = "" 
            .EvenPage.LeftFooter.Text = "" 
            .EvenPage.CenterFooter.Text = "" 
            .EvenPage.RightFooter.Text = "" 
            .FirstPage.LeftHeader.Text = "" 
            .FirstPage.CenterHeader.Text = "" 
            .FirstPage.RightHeader.Text = "" 
            .FirstPage.LeftFooter.Text = "" 
            .FirstPage.CenterFooter.Text = "" 
            .FirstPage.RightFooter.Text = "" 
        End With 
         '    Next
        ActiveSheet.Cells.EntireColumn.AutoFit 
         
         
    Next ws 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi All,

I have the following code (see end of message) that should select only a certain number of worksheets from the original file and then save the selected files as a new work book.

The reason I want to do this is because I would like to share the workbook with other people, however not all of the workbook because the original file has sensitive data in it. The idea is that by pressing a button a predefined selection of worksheets are copied and then saved in a separate workbook.

This is currently half working.

Basically at the moment if I comment out the line:
'ws.Cells.PasteSpecial Paste:=xlValues

The process works fine. The problem however is that the copied workbook contains links to the original file. Consequently if I e-mail this sheet my values appear as broken links (i.e. ####) .

I really need to get the Past Values option to work – any help would be much appreciated.

Regards,

Kevin

Sub Button9_Click()

Dim NewName As String
Dim nm As Name
Dim ws As Worksheet

If MsgBox("Copy the 'Quarterly Report' sheet to a new file to e-mail to DAAT" _
, vbYesNo, "NewCopy") = vbNo Then Exit Sub

With Application
.ScreenUpdating = False

' Copy specific sheets
' *SET THE SHEET NAMES TO COPY BELOW*
' Array("Sheet Name", "Another sheet name", "And Another"))
' Sheet names go inside quotes, seperated by commas

On Error GoTo ErrCatcher
Sheets(Array("i. Guidance", "ii. Contents", "1. Perf", "2. InfoNeeds", "3. ServDevel", "4. StaffDevel", "6. Incidents", "7. Partnership", "8. Finance")).Copy
'Modules(Array("Module1", "Module2")).Copy
On Error GoTo 0

' Paste sheets as values
' Remove External Links, Hperlinks and hard-code formulas
' Make sure A1 is selected on all sheets

For Each ws In ActiveWorkbook.Worksheets

ws.Cells.Copy
'ws.Cells.PasteSpecial Paste:=xlValues
ws.Cells.Hyperlinks.Delete
Application.CutCopyMode = False
Cells(1, 1).Select
ws.Activate
Next ws
Cells(1, 1).Select

' Remove named ranges
For Each nm In ActiveWorkbook.Names
nm.Delete
Next nm

' Input box to name new file
NewName = InputBox("Please Specify the name of your new workbook", "New Copy")

' Save it with the NewName and in the same directory as original
ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "" & NewName & ".xls"
ActiveWorkbook.Close SaveChanges:=False

.ScreenUpdating = True

MsgBox ThisWorkbook.Path & "" & NewName & ".xls saved."
End With

Exit Sub

ErrCatcher:

MsgBox "Specified sheets do not exist within this workbook"

End Sub

Alright so this is a bit of an odd request, but here goes.

I've got a game I've been working on for a while and I frequently need to make adjustments here and there. The problem is that doing this is extremely cumbersome as I have to scroll around forever and blah blah blah.

What I would love to be able to do is to somehow link cells within Excel to certain segments of data in these text documents that hold the data that needs to be adjusted. Let me explain a bit further:

Step 1: Define the state
I'll have a cell (A1) that looks for the state. I enter the state number, (for this case, let's say 200). The text appearing before the state is always "[statedef x]" with "x" being a number. So I'll put in "200" in this cell and excel will scan the document in question for "statedef 200"

Step 2: Find animation number
Excel will then look for the next animation number and enter it into the cell next to it. Animations are always defined like this "anim = x". In this case, Excel comes up with "202" and puts it in cell B2.

Step 3-6: Find other values.
Basically it's going to search for other values and place it in the cells that I want them to appear in.

So far, this is half of what I would like to do. After all the data has been grabbed, It would absolutely magical if I could change certain values in excel and excel would then write said values to the text document. For instance, if I wanted to change the animation number from 202 to 220, I could enter the value into excel, save, and the value would be updated in my code.

The whole point of this is to save me a great deal of time with making minor tweaks here and there and have something that's easier to look at when viewing the most essential data of this game. I often have to scroll through 4-5000 lines of code just to change the values under 20 to 30 lines. Having this sort of thing would speed things up for me greatly.

What I'm asking is, is this sort of thing possible with excel? If it is, how should I approach it?

Hi, i have designed a loop statement that looks to a external workbook to retrieve data. This workbook(wb2) contains sheets named after years from 1992 to 2003. Each Worksheet contains information for about 120 different catergories. Within my original workbook(wb1) 15 or so of the catergories have there own worksheet, my code will then open up wb2 and the first sheet (1992) and return the data series for the particular catergory, it then moves on to next year (1993) and so on. At the moment i'm telling it exactly how to stop e.g loop until iCell = "2004"

I would like to improve this to say something along the lines of if worksheet(myVariable called sYear) is nothing then exit the loop. I've been experimenting with

Do ' start loop
sYear = Ocell.Text ' this value will change on each loop
wb2.Activate
Set wsI = Worksheets(sYear) ''year to search for
wsI.Activate ' make the intermediate sheet active
Range("A3").Select
wsI.Cells.Find(sIO).Select '

Set Ocell = Ocell.Offset(0, 1) ' go to next row in series
Loop Until wsI Is Nothing

Unfortunatly this effort only makes it as far as the line in red e.g it will loop until sYear = 2005 and then stops as no worksheet is called 2005 thus producing error and breaking the loop anyway.
Can anyone see an easy approach around what i'm trying to do? thanks

Hi,

I have a spreadsheet which has raw data inside which is copy/pasted from multiple different systems and thus one of the columns 'structure' varies dependent on the original source of the data. This isn't too much of a problem as I only need the 'number' from each column.

A couple of examples of entries are (one line per cell)

ABC OVEN CO LTD 35699459 L G
MAGICLTD 3455355 G G D
FISH4LIFE LTD 3224223 D S
SNOW 2 GO LTD 544566 FG

Now I did originally plan on looping through the characters until I encountered the first number, then making a note, going through to the last number and making the last number as the next note then using MID or similar to pull that number out.

Now obviously some of the entries have 'numbers' within the names at the start, I'm only interested in >3 digits ...

Is my method the best way? anyone got any better ideas? maybe some code samples?

I have hundreds of rows to loop through so speed is an issue too, I need to extract the number and then place in the next column.

Many thanks
Mike

Ok I'll try my best to explain the issue.

ok in the example workbook I uploaded I'm capturing data from a sheet called "scientist" from column B then searching the sheets "dummy data" and "dummy data 2" in column A for the value captured from scientist (variable is declared as objValue).

my problem is that my code is giving me the result I need, which is to search, offset across one column, capture active cell (value is declared as objStatus) then return to sheet "scientist" and offset across and paste value.

Then I have it looping and it works fine.

My issue is that I will be capturing a value of say "Tractor" then searching for it. But if for example "Tractor_wheel" comes before "Tractor" it will stop and take the value from the adjacent cell of "Tractor_wheel" instead of "Tractor" which is what I need.

It gets more complicated because some of the cells I have to search have multiple data such as "bike , bike_wheel , bike_handle , bike_damaged" and to verify that I have found "bike" i'm kind of stuck on how to make it so that it will search within that cell then check that it is an exact text match (doesnt need to be case sensitive)

If that even remotely makes sense let me know and I'll try to be clearer if necessary. My current code can be found in the excel sheet but I will paste it below as well.

I've tried to notate all that the code is suppose to be doing so it should be easy to follow along.

Sub searchForStatus()

'declaring variables
'value that we will search with
    Dim objValue As String
'status after we conduct search
    Dim objStatus As String
'variable to capture sheet value found in
    Dim statSheet As String
'capturing the string length of objValue
    

'select correct workbook
    Windows("dummy doc.xls").Activate
    
'select correct sheet
    Sheets("scientist").Select

'selecting column B
    Columns("b:b").Select
    
'will loop from here
loopPoint:
    
    'offseting the value by one row (below is line 18)
    ActiveCell.Offset(rowOffSet:=1, columnOffset:=0).Activate

'if function to test is current selection is empty
    If ActiveCell.Value = "" Then
    GoTo endScript
    Else
    'do nothing
    End If

'capture current selection in variable objValue
    objValue = ActiveCell.Value

'select correct workbook
    Windows("dummy doc.xls").Activate


    'select legal data sheet (can be adjusted to look in legal doc once working in dummy doc)
        Sheets("dummy data").Select
    
    'selecting column A for searching
        Columns("A:A").Select
        
    'search cells
    
    On Error Resume Next

    Cells.Find(What:=objValue, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, MatchCase:=False,
SearchFormat:=False).Activate
            If Err.Number = "91" Then
            GoTo nextSheet
            Err.Clear
        End If
    'if found go to ifFound
        GoTo ifFound

nextSheet:
    'select legal data sheet (can be adjusted to look in legal doc once working in dummy doc)
        Sheets("dummy data 2").Select
    
    'selecting column A for searching
        Columns("A:A").Select
        
    'search cells
    
    On Error Resume Next
    Cells.Find(What:=objValue, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, MatchCase:=False,
SearchFormat:=False).Activate
        If Err.Number = "91" Then
            GoTo notEntered
            Err.Clear
        End If
    'if found go to ifFound
        GoTo ifFound

ifFound:
    
'capturing data
    'offset cell to the status column
        ActiveCell.Offset(rowOffSet:=0, columnOffset:=1).Activate
    'capture current status
        objStatus = ActiveCell.Value
    'capture current sheet
        statSheet = ActiveSheet.Name

'entering found data
    'select correct workbook
        Windows("dummy doc.xls").Activate
    'select correct sheet
        Sheets("scientist").Select
    'offseting the value by one row
        ActiveCell.Offset(rowOffSet:=0, columnOffset:=1).Activate
    'insert sheet name
        ActiveCell.Value = statSheet
    'offseting the value by one row
        ActiveCell.Offset(rowOffSet:=0, columnOffset:=1).Activate
    'insert captured value as status value
        ActiveCell.Value = objStatus
    'return to column B
        ActiveCell.Offset(rowOffSet:=0, columnOffset:=-2).Activate
    'loop script
    GoTo loopPoint:

notEntered:
    'select correct workbook
        Windows("dummy doc.xls").Activate
    'select correct sheet
        Sheets("scientist").Select
        ActiveCell.Offset(rowOffSet:=0, columnOffset:=1).Activate
    'insert sheet name
        ActiveCell.Value = "not entered"
    'offseting the value by one row
        ActiveCell.Offset(rowOffSet:=0, columnOffset:=1).Activate
    'insert "not entered" as status value
        ActiveCell.Value = "not entered"
    'return to column B
        ActiveCell.Offset(rowOffSet:=0, columnOffset:=-2).Activate
    'loop script
    GoTo loopPoint:
  
endScript:
    
End Sub


Having trouble with macro. I want macro to search all sheets in
workbook, find a word, copy the cell and adjacent cells with that word,
then paste the data into another workbook.

Here are the steps I follow:
*Open two documents, one called Hierarchy.xls and one called
Harvest.xls
*In Harvest.xls, I have the appropriate cell focused
*Switch to working in Hierarchy.xls
*Cmd+f(find)>Find what:searchword>Within:Workbook>Search:By Rows>Look
in:Formulas (match case & find entire.. are not checked)>Find
Next>Close
*Hierarchy.xls has 33 sheets. I make sure that the cell focus is on A1
of each sheet.
*Select the first worksheet
*Tools>Macro>Record New Macro
*Macroname:Macro2>Shortcut Option+Cmd+q>Store in: This Workbook>OK
*Relative reference is depressed
*Cmd+f(find)
*Click Find Next
*Click Close
*Left arrow once>Hold Shift, right arrow twice (effectively selecting
the adjacent cells to the found cell)
*Cmd+c(copy)
*>Window>Harvest.xls(switches to other workbook)
*Cmd+v(paste)
*down arrow once (gets to next row, ready for future applications of
macro)
*>Window>Hierarchy.xls(switches to other workbook)
*down arrow once (gets to next row, ready for future applications of
macro)
*Esc (gets rid of marquee on selection)
*Click Stop Macro (macro is complete)
*Go back to first sheet (search switched to second sheet)
*Cmd+option+q (activates macro)

First couple times I use the macro, it works fine, but after the third
time, I get error: "Run-time error '1004' Method 'Offset' of object
'Range' failed.

*Click Debug

"ActiveCell.Offset(0,-1).Range("A1:C1").Select" is highlighted.

Here is the complete text of the vba macro:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 1/3/2006 by L
'
' Keyboard Shortcut: Option+Cmd+q
'
Sheets("Sheet2").Select
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(0, -1).Range("A1:C1").Select
Selection.Copy
Windows("Harvest.xls").Activate
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range("A1").Select
Windows("HIERARCHY.xls").Activate
ActiveCell.Offset(1, 0).Range("A1").Select
Application.CutCopyMode = False
End Sub

Notes: The first line troubles me, because I think the macro is
automatically switching to the second sheet, when it should just be
searching and not switching sheets.

Select a cell that has leading zeroes, and post back with exactly what the
cell displays, and exactly what the formula bar displays.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"postitnote" <postitnote@discussions.microsoft.com> wrote in message
news:DA5D6838-5796-4AEA-9685-9606D973587F@microsoft.com...
> I just sorted that same data as well and it also worked for me. There
must
> be something within the spreadsheets that were given to us that is messing
it
> up because the last four digits I used in my example were taken directly
from
> SSNs that were out of order.
>
> One thing is that the SSN is formatted as an SSN (format cells, number,
> special, SSN). When I change this to a regular number, the 0's in the
> beginning and end disappear.
>
> I just tried your system with some SSNs that began with 0 and this is what
> happens:
>
> The SSN is 000-42-0400 for example. When I do Text to Column and it comes
> up in the box to put in the dividers, it looks like this:
>
> 420400
>
> ...because the 0s in the front disappear. I have no choice but to put the
> dividers like this:
> 420 | 40 |
>
> ...because that is where the lines go for SSNs that do not have 0s missing
> from them. So when it is broken down, it goes into sort boxes that way.
So
> if I have two SSNs, 000-42-0400 and 123-45-6789, they look like this in
the
> extra columns:
>
> 420 40
> 123 45 67 89
>
> ...and get sorted as such.
>
> I don't mean to be a pain, and your suggestion is great! Can you think of
> what could be causing it to do this? It has to be something within the
> spreadsheet, the way the cells are formatted, because I get the correct
> output when I hand type sample SSNs into a new spreadsheet.
>
> Thank you.
>
>
>
>
> "Ragdyer" wrote:
>
> > I just sorted your example numbers, following the instructions that I
posted
> > here, and they sorted *exactly* as you stated that they *should be*.
> >
> > The procedure works fine!
> >
> > Say the original numbers are in Column A, and the 6 parsed (separated)
> > columns are B to G.
> > B = first 3 numbers
> > C = dash
> > D = middle 2 numbers
> > E = dash
> > F = first 2 numbers of last set
> > G = last 2 numbers
> >
> > After the 6 columns were parsed along side the original data, did you
select
> > *all* columns.
> > Did you then set the *first* sort key as Column G (last 2 numbers),
> > *Second* key as Column F,
> > And *third* sort key as Column D?
> >
> > It worked perfectly for me!!!
> > --
> > Regards,
> >
> > RD
> >
>
> --------------------------------------------------------------------------
-
> > Please keep all correspondence within the NewsGroup, so all may benefit
!
>
> --------------------------------------------------------------------------
-
> >
> >
> > "postitnote" <postitnote@discussions.microsoft.com> wrote in message
> > news:38BA77F6-7318-4AC1-A376-49D07BC2CF6C@microsoft.com...
> > > Ok now that I went and wrote that everything worked fine I have to
write
> > > again and say it didn't work when I actually put all 45K names
together.
> > > Here is what is happening (again, with the 0's)...
> > >
> > > I have the following SSNs:
> > > 111-11-4030
> > > 222-22-0400
> > > 333-33-7040
> > > 444-44-0500
> > > 555-55-0300
> > > 666-66-0040
> > >
> > > In TDO, they should show up like this:
> > > 555-55-0300
> > > 222-22-0400
> > > 444-44-0500
> > > 111-11-4030
> > > 666-66-0040
> > > 333-33-7040
> > >
> > > ...but instead they are showing up like this:
> > > 111-11-4030
> > > 555-55-0300
> > > 666-66-0040
> > > 333-33-7040
> > > 222-22-0400
> > > 444-44-0500
> > >
> > > ...which is not TDO. I believe this is happening because of the 0's
that
> > > are disappearing at the end of the SSN, but I don't know for sure.
Any
> > > suggestions?
> > >
> > >
> > >
> > > "RagDyer" wrote:
> > >
> > > > Since your *original* data is *untouched*, I don't see what
difference
> > that
> > > > makes.
> > > >
> > > > You're going to sort on 4 ... right?
> > > >
> > > > You're going to throw it (04, 4)away when you're done anyway!
> > > >
> > > > Perhaps you're missing the original concept of this procedure.
> > > > --
> > > > Regards,
> > > >
> > > > RD
> > > > --------------------------------------------------------------------
> > > > Please keep all correspondence within the Group, so all may benefit!
> > > > -------------------------------------------------------------------
> > > >
> > > > "postitnote" <postitnote@discussions.microsoft.com> wrote in message
> > > > news:9EE9AF0A-8E2F-4F86-9CCA-F14B12ED0FC2@microsoft.com...
> > > > > Ok, I tried all of the options given here and this one seems to be
the
> > > > best
> > > > > solution. My only concern now is that the spreadsheets were given
to
> > us
> > > > set
> > > > > up as SSNs and Numbers and not as text so whenever I make it text
to
> > > > column
> > > > > and one of the columns has a "04" in it, then only the four shows
up.
> > I
> > > > have
> > > > > over 11K names and SSNs on one list (and five lists) so how can I
make
> > > > those
> > > > > 0's appear without having to go thru each and every number to
input
> > the 0
> > > > > manually?
> > > > >
> > > > > Thank you!
> > > > >
> > > > >
> > > > >
> > > > > "RagDyer" wrote:
> > > > >
> > > > > > Just repeating, TTC is *perfect* for what you're looking to do.
> > > > > >
> > > > > > Select your column of numbers, then,
> > > > > > <Data> <Text To Columns> <Fixed Width> <Next>
> > > > > > Then create 5 break lines, separating your last set of 4 digits
in
> > the
> > > > > > middle and then separating out the dashes from the other
numbers.
> > > > > > Then click <Next>
> > > > > >
> > > > > > Now, change the address in the "Destination" box, to a column
where
> > > > there's
> > > > > > room to print out the 6 columns, and also this allows the
original
> > > > column of
> > > > > > numbers to remain intact, where they were.
> > > > > >
> > > > > > Now click <Finish>
> > > > > >
> > > > > > You have your original data, with adjoining columns parsed out
the
> > way
> > > > you
> > > > > > separated them.
> > > > > > Now select them all, and sort in any order that you wish.
> > > > > >
> > > > > > When you're done, throw away those extra columns.
> > > > > > --
> > > > > > HTH,
> > > > > >
> > > > > > RD
> > > > > > ==============================================
> > > > > > Please keep all correspondence within the Group, so all may
benefit!
> > > > > > ==============================================
> > > > > >
> > > > > > "postitnote" <postitnote@discussions.microsoft.com> wrote in
message
> > > > > > news:D312381B-518B-4CC8-803A-D6493D7D9511@microsoft.com...
> > > > > > > This sounds like a very good idea though after looking at my
> > > > spreadsheet I
> > > > > > > must admit that I'm not sure what to do with this information.
> > What
> > > > is a
> > > > > > > "helper column" and how do I get it? I did input the
information
> > you
> > > > gave
> > > > > > me
> > > > > > > (=RIGHT(G1,4)&MID(G1,5,2)&LEFT(G1,3)) and noticed how it
changed
> > the
> > > > > > column,
> > > > > > > but it's not exactly what I need (see my reply to the post
made by
> > Ken
> > > > in
> > > > > > > this thread).
> > > > > > >
> > > > > > > Thank you for your help.
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "Ron Rosenfeld" wrote:
> > > > > > >
> > > > > > > > On Tue, 7 Jun 2005 12:30:02 -0700, "postitnote"
> > > > > > > > <postitnote@discussions.microsoft.com> wrote:
> > > > > > > >
> > > > > > > > >I work in a medical records office and we are attempting to
> > combine
> > > > > > five
> > > > > > > > >years' worth of records onto one spreadsheet. Our office
uses
> > > > Terminal
> > > > > > Digit
> > > > > > > > >Order (or TDO) and it doesn't appear that Excel can sort
this
> > way
> > > > as it
> > > > > > is in
> > > > > > > > >order with the last four digits, then the middle two, then
the
> > top
> > > > > > three.
> > > > > > > > >Does anyone know if there is a way to do this without
having to
> > put
> > > > the
> > > > > > SSN
> > > > > > > > >in three separate columns and then merging the columns
together
> > > > > > somehow?
> > > > > > > >
> > > > > > > > The simplest method would be to add a "helper column" that
has
> > the
> > > > digit
> > > > > > groups
> > > > > > > > in the order to be sorted -- then sort on that column.
> > > > > > > >
> > > > > > > > If your digit groups are separated by dashes (and are
located in
> > > > column
> > > > > > G),
> > > > > > > > then:
> > > > > > > >
> > > > > > > > =RIGHT(G1,4)&MID(G1,5,2)&LEFT(G1,3)
> > > > > > > >
> > > > > > > > will return a text string of the digits in the order you
> > specified.
> > > > > > > >
> > > > > > > > Include this helper column in your sort table, and sort on
that
> > > > column.
> > > > > > You
> > > > > > > > can then delete or hide the helper column.
> > > > > > > >
> > > > > > > > If it is going to be a repetitive task, it can be automated
> > using a
> > > > > > macro.
> > > > > > > >
> > > > > > > > If your numbers are NOT separated by dashes, but are rather
a
> > > > sequential
> > > > > > > > string, then use this formula:
> > > > > > > >
> > > > > > > > =RIGHT(TEXT(G1,"000-00-0000"),4)&MID(TEXT(
> > > > > > > > G1,"000-00-0000"),5,2)&LEFT(TEXT(G1,"000-00-0000"),3)
> > > > > > > >
> > > > > > > >
> > > > > > > > --ron
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >

This is an attempt to execute a FindNext.
The user enters a PO# in a UserForm, and after clicking OK, UserForm13
comes up, with all the data pertaining to that PO. That works fine. I
put another button in that form to request to Find the Next Record.
with the same PO, if there is any.

That button's code if below. What I want is for the cell that
contains that next PO to be named "EditPO".

Then, I want to unload the present UserForm13 (which has the data from
the 1ts Find). Then reload UserForm13, which will show the data for the
next record.

Is there something I can do to make the Range Name Add statement below
work within
this procedure? Or, is it plain I'm not going about this correctly?
I tried playing around with different ways to reproduce the "C."
code in the line where I'm trying to name the range.

Thanks for any help you can offer.
J.O.

Private Sub CommandButton3_Click()
'Find Next Record

With Worksheets("Official List").Range("j6:j65536")
Set C = .Find(FindPOVal, LookIn:=xlValues)
If Not C Is Nothing Then
firstAddress = C.Address
Do
'C.Interior.Pattern = xlPatternGray50 (this line is from
the Help Example - left here as reference while figuring out how to
code my next command line, to name range, see next line.)

ActiveWorkbook.Names.Add Name:="EditPO",
RefersTo:=FoundCell
Unload UserForm13
UserForm13.Show

Set C = .FindNext(C)

Loop While Not C Is Nothing And C.Address <> firstAddress
End If
End With
End Sub

i am doing something in a pre designed excel spreadsheet

I have a column a cell where i am entering the following data.

Enter the weight of each item below. To finish calculating enter the
value 0.

after the word "Below" i press the ENTER key, to make the rest of the text
go onto the next line, this doesn't happen and it goes down to the next cell

can anyone tell me how i can make the text after the word "belo" appear on a
new line within the same cell

I am using Office XP

this is for a dry run table of a c++ program i have developed, ironic how a
student in advanced programming cant figure out how to put text in a cell
onto a new line in the same cell

it is because the particular cell is too wide i need it so i can print this
out on one sheet of A4 and having to stretch this particular cell to fit
that text means i cannot do this

so anyone got any suggestions i would greatly appreciate it

Thanks

Chris

Hi
How do I go about combining the folowing functions in the attached file

I need table 1 to look like table 2 (or something similiar) obviously a bigger file

so basically delete blanks and shift left (but ideally only within a few columns so not column j for example)

Then insert the required the number of rows transposed in a column.

then repeat for the next line

Hope that makes sense, thanks for any help

'this is all I have so far, almost works but not sure how to make it repeat for next line

Range("d2:k2").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft
'calculate non blank cells
n = Worksheets("Sheet1").Range("D2:K2").Cells.SpecialCells(xlCellTypeConstants).Count
'insert that number of cells
Range("d2:k2").Offset(1).EntireRow.Resize(rowsize:=n - 1).Insert Shift:=xlDown
'copy data
Range(Cells(2, 5), Cells(2, 3 + n)).Copy
'transpose all except the first
Range("d2").Offset(1).PasteSpecial xlPasteValues, Transpose:=True


Having trouble with macro. I want macro to search all sheets in
workbook, find a word, copy the cell and adjacent cells with that word,
then paste the data into another workbook.

Here are the steps I follow:
*Open two documents, one called Hierarchy.xls and one called
Harvest.xls
*In Harvest.xls, I have the appropriate cell focused
*Switch to working in Hierarchy.xls
*Cmd+f(find)>Find what:searchword>Within:Workbook>Search:By Rows>Look
in:Formulas (match case & find entire.. are not checked)>Find
Next>Close
*Hierarchy.xls has 33 sheets. I make sure that the cell focus is on A1
of each sheet.
*Select the first worksheet
*Tools>Macro>Record New Macro
*Macroname:Macro2>Shortcut Option+Cmd+q>Store in: This Workbook>OK
*Relative reference is depressed
*Cmd+f(find)
*Click Find Next
*Click Close
*Left arrow once>Hold Shift, right arrow twice (effectively selecting
the adjacent cells to the found cell)
*Cmd+c(copy)
*>Window>Harvest.xls(switches to other workbook)
*Cmd+v(paste)
*down arrow once (gets to next row, ready for future applications of
macro)
*>Window>Hierarchy.xls(switches to other workbook)
*down arrow once (gets to next row, ready for future applications of
macro)
*Esc (gets rid of marquee on selection)
*Click Stop Macro (macro is complete)
*Go back to first sheet (search switched to second sheet)
*Cmd+option+q (activates macro)

First couple times I use the macro, it works fine, but after the third
time, I get error: "Run-time error '1004' Method 'Offset' of object
'Range' failed.

*Click Debug

"ActiveCell.Offset(0,-1).Range("A1:C1").Select" is highlighted.

Here is the complete text of the vba macro:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 1/3/2006 by L
'
' Keyboard Shortcut: Option+Cmd+q
'
Sheets("Sheet2").Select
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(0, -1).Range("A1:C1").Select
Selection.Copy
Windows("Harvest.xls").Activate
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range("A1").Select
Windows("HIERARCHY.xls").Activate
ActiveCell.Offset(1, 0).Range("A1").Select
Application.CutCopyMode = False
End Sub

Notes: The first line troubles me, because I think the macro is
automatically switching to the second sheet, when it should just be
searching and not switching sheets.


No luck finding an answer? You could always try Google.