Posts by rem1224

rem1224 · Sep 5th 2003

Here's a different solution that worked for me:

1. Open the HTML document and copy the table to the clipboard.

2. Open Notepad and paste the clipboard into a new Notepad file - Save the notepad file (example: testfile.txt)

The file should probably look something like:

1:24 4:35 12:56 62:34
12:34 6:15
2:33 6:50 9:30
etc

depending on how many rows/col were in the HTML table

3. Open the testfile.txt in Excel - you should get a text import wizard - - Pick 'Delimited' as the data type, on the next screen set the delimiter to 'space' - on the next screen set the column data format to 'text' for each column - then click 'finish'

4. The data should now be Text Only in the excel sheet. You can copy and paste this data into the actual worksheet where you need it (make sure the destination cells are formatted as Text)

.....Ralph

rem1224 · Sep 5th 2003

Going back to Will's question - Where is the data you are pasting into the Excel sheet coming from (where did you copy it from).

If it is plain text - like coming from a file like "filename.txt", then I'm really not sure why the earlier solution is not working.

If you are copying the data from another Excel sheet or another spreadsheet / database program and the data in that program is actually in time format, then you are not really copying "4:35" but the time value (the decimal time as you called it) so that is the only thing that the clipboard has to paste in the cell - regardless of how its formatted.

If that is the case, you will have to convert the time value to a text string BEFORE you copy and paste it.

If that can't be done, you can paste it into excel as the time value, then convert it to text, then copy and paste values on the text to get rid of the conversion formula - - see the following sheet with formulas

.....Ralph

<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=5><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left><FONT color=white>Microsoft Excel - Book2</FONT></TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: xl2002 XP : OS = Windows XP </FONT></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=5><TABLE width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption">(<U>F</U>)ile (<U>E</U>)dit (<U>V</U>)iew (<U>I</U>)nsert (<U>O</U>)ptions (<U>T</U>)ools (<U>D</U>)ata (<U>W</U>)indow (<U>H</U>)elp <A onclick=show_popup(); href="#javascript:void(0)">(<U>A</U>)bout</A></TD><TD vAlign=center align=right><FORM name=formCb605117><INPUT onclick='window.clipboardData.setData("Text",document.formFb202339.sltNb447362.value);' type=button value="Copy Formula" name=btCb942116></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=5><TABLE border=0><TBODY><TR><FORM name=formFb202339><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb202339.txbFb150492.value = document.formFb202339.sltNb447362.value" name=sltNb447362><OPTION value='=TEXT(A3,"h:mm")' selected>B3</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8><B>=</B></TD><TD align=left bgColor=white><INPUT size=80 value='=TEXT(A3,"h:mm")' name=txbFb150492></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><BR></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>A</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>B</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>C</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>D</CENTER></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>1</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">Time Formatted</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">Text Converted</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">Pasted Value</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>2</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">This number is Time</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">This is =TEXT(A3,"h:mm")</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">This is Copy B3 - Paste Values to C3</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>3</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #0000ff; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">4:35</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #0000ff; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"><A onclick="document.formFb202339.sltNb447362.options[0].selected=true; document.formFb202339.txbFb150492.value = document.formFb202339.sltNb447362.value;" href="#javascript:void(0);">4:35</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #0000ff; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">4:35</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>4</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" colSpan=5><TABLE width="100%" align=left VALIGN="TOP"><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; WIDTH: 120pt; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff" align=left><U>Sheet1</U></TD><TD> </TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE><BR><FONT color=#339966 size=1>[HtmlMaker 2.41] </FONT><FONT color=#339966 size=1>To see the formula in the cells just click on the cells hyperlink or click the Name box</FONT><BR><FONT color=red size=1>PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</FONT></CENTER>

rem1224 · Sep 5th 2003

Hi datovoz,

Highlight the column, select Format / Cells and under number, change the category to TEXT. That will force text mode and anything you enter will be text.

Hope this helps

.....Ralph

rem1224 · Sep 5th 2003

Hi Beeker,

Just tell the operator not to do that :biggrin::tumble:

Seriously though, I added a test to see if the update is a duplicate - shouldn't write double records now.

.....Ralph

rem1224 · Sep 5th 2003

You can hide any sheet with menu Format / Sheet / Hide. At least 1 sheet must be visible.

You can show a blank sheet behind your userform and change the "fill Color" of all cells to any color to make it look like a solid background.

.....Ralph

rem1224 · Sep 4th 2003

You can set up a Query
Data / Import External Data / New Database Query

If not already set up, you will have to set up the Data Source to be "Microsoft ODBC for Oracle". Then you should be able to attach to the data source and write the query in SQL.

.....Ralph

rem1224 · Sep 4th 2003

Hi -

You can put a check box / combo box in a cell, then select / highlight the cell, grab the anchor (bottom right corner), left-click and drag down just like you would for a value or formula.

All formatting control will be duplicated, including the cell reference - copying won't update the cell reference address like a formula does - so you still have to edit the controls individually.

.....Ralph

rem1224 · Sep 4th 2003

I don't have 97, so this is a real guess - but maybe vbTextCompare was not in the 97 VBA Type Library? Try looking up "Constants" in the 97 VBE help file to see if it's there.

(Does it crash Excel or return an actual error code/message?

.....Ralph

rem1224 · Sep 4th 2003

Hi Beeker,

Have a look at the attached.
I added a macro to your file to copy over all the data on the Week End sheet to the individual sheets.

If the employee does not have a sheet, a new sheet is created and formatted (the sheet has to be named the exact same as the data in Col A).

A new line is added on to the end of the employees sheet with the current week and data.

Hope this helps

.....Ralph

rem1224 · Sep 3rd 2003

Hi Graeme,

Something like this worked for me to get rid of internal names where the Refers to looks like =#REF!$G$11:$N$12

Sub Macro1()
Dim Nm As Name

For Each Nm In ActiveWorkbook.Names
If Mid(Nm, 1, 5) = "=#REF" Then
Nm.Delete
End If
Next
End Sub

.....Ralph

rem1224 · Sep 3rd 2003

A couple of other ideas,

Check the icon / shortcut you're using to start excel and see if somehow the filename "normal.dot" got tagged onto the end of the command line.

Check in Excel under Tools / Options / General and make sure the field for "At startup, open all files in:" is blank.

Check the Visual Basic Editor (Alt-F11 from within Excel) for any addins that are trying to load that file.

.....Ralph

rem1224 · Sep 3rd 2003

Hi Peter,
It's a bit more difficult than normal because you have a lot of records that are very similar (same surname, forename, prefix, etc).

See the attached sheet. I added an column to each worksheet to create a KEY for each record (combined surname, forename, prefix, and job title to try to get something unique). Next used the VLOOKUP function to pull the source and abc code from the old to the new. The #NA shows where there is no match.

Hope this gets you started.

.....Ralph

rem1224 · Sep 3rd 2003

It may be overkill on the calculations and slow things down too much, but you could put the CalculateFull in the change event macro for that sheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.CalculateFull
End Sub

After you change the color of the cell and leave the cell, the function will update.

.....Ralph

rem1224 · Sep 3rd 2003

Looks like Derk and I had our fingers on the Post button at the same time. :biggrin:

Welcome back, Derk - hope you had a great vacation....

rem1224 · Sep 3rd 2003

Hi Kelly,

You could try something like this:

Code

Sub EditFilteredRange()
Dim rng As Range
Set rng = Range("A2", Range("A65536").End(xlUp)).SpecialCells(xlCellTypeVisible)
   For Each cell In rng
         ' Your Code Goes Here
   Next cell
End Sub

assumes that Row 1 is your header row, so it doesn't pick it up.

Hope this helps

.....Ralph

rem1224 · Sep 3rd 2003

Hi,

Which variables in the routine do you want to limit?

In your For/Next loops you can skip numbers by using:
For j = 1 To 30 Step 3
This will use 1,4,7,10, etc in the loop. Not sure if that is what you wanted.

.....Ralph

rem1224 · Sep 3rd 2003

Hi CoMaboy,

Sounds like the normal.dot file is in the wrong location. Look for a subdirectory called "xlstart". It will be somewhere in the tree below Microsoft Office. The normal.dot file should not be in that directory. (I put a copy there to test and got the same error as you mention).

Normal.dot is a Word file where the default document parameters (tabs, margins, fonts, etc) are stored. It's usually in a directory called "templates".

Hope this helps

.....Ralph

rem1224 · Sep 2nd 2003

Hi silken,

Dave has some good examples of math by colors at this link
http://www.ozgrid.com/VBA/Functions.htm

You'll need some VBA and a user defined function to do what you want.

I've attached an example file using the UDF from the link above.

Hope this helps

.....Ralph

rem1224 · Sep 2nd 2003

Hi -

Is your word document all formatted like your example, i.e.

AA456789 GENE
BB123455 PAUL
CC123 JOHN
ZZ9999 JANE

etc.

If so, it would be a lot easier to perform a "convert text to table" operation in Word, then copy and paste the table into excel. Then you could do your cell merges with VLOOKUP and CONCANTENATE functions.

(is this the same question you posted today under VBA: Help with Excel and parsing a txt document ?)

Hope this helps

.....Ralph

rem1224 · Sep 2nd 2003

Hi Bryan,

I mis-read your original post - thought you were trying to do something else - -anyway - -

You might try the following code, it left justifies text and centers numbers (doesn't matter what the length of the entry is)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim myRange As Range, temp As Integer

Set myRange = Range("B:B")
If Intersect(Target, myRange) Is Nothing Then Exit Sub

On Error GoTo ItsText
temp = Target.Value * 1 'test to see if it's a number

Target.HorizontalAlignment = xlCenter
Exit Sub

ItsText:
Target.HorizontalAlignment = xlLeft

End Sub

.....Ralph

Posts by rem1224

VBA: force text modefor a column

VBA: force text modefor a column

VBA: force text modefor a column

[Solved] attendance sheet-copy formula

Hide background for UserForm

Importing: Getting Data from oracle

Controls (Control Toolbox): Check Box in the Cells

[Solved] How to Delete specific named ranges in a workbook

[Solved] attendance sheet-copy formula

[Solved] How to Delete specific named ranges in a workbook

[Solved] Error message: Normal.dot file format not valid

[Solved] matching data and copying

updating calculations

VBA: for..each..next on visible rows in filtered list

VBA: for..each..next on visible rows in filtered list

hello there

[Solved] Error message: Normal.dot file format not valid

Conditional Formatting: Action: Shading cells Output: Popul

VBA: Parsing with Excel and Word

[Solved] VBA: Cell alignment by content