PDA

View Full Version : [SOLVED] Help with Autoformat pls



fwawergurl16
08-13-2007, 08:19 PM
Hi. Im an intern in Malaysia. I've cleaned up some spreadsheets & consolidated them. The problem is that when I consolidate, my data is changed from text to date format.How can I make it stay as text when I consolidate? :help

Eg: Original text: 5/13, 10/53, 8/37 (it's column size per cm)
After consolidation: May-13, October-53, August-37

Does any1 know how to get about this? All help much appreciated! Thanks!!

fwawergurl16 :think:

anandbohra
08-13-2007, 10:57 PM
set the format of the cell as text then type or paste your data it will remain 5/13 instead of May-13

fwawergurl16
08-13-2007, 11:18 PM
set the format of the cell as text then type or paste your data it will remain 5/13 instead of May-13
I did that already, but it doesn't work. I'm not sure if my codes are correct also..below's the code. If it's incorrect, would u know how to correct it? Thanks loads!


Sub formatMasterCol()
Columns("I:I").Select
Selection.NumberFormat = "@" 'Text
End Sub

geekgirlau
08-13-2007, 11:31 PM
When you say you are "consolidating" them, what exactly are you doing?

fwawergurl16
08-13-2007, 11:43 PM
When you say you are "consolidating" them, what exactly are you doing?
I posted for help on the consolidation work last time in this forum (Solved: Copy Paste using macro involving 6 different workbooks (http://www.vbaexpress.com/forum/showthread.php?t=13975&highlight=6+sheets)). Well, what happens when macro runs:
1. prompts user to select folder to clean
2. cleans all excel sheets in folder (several Excel sheets inside. Each .xls has one sheet only)
3. create a new folder called 'Cleaned' & dumps every cleaned file into it with a saved as name (new name)
4. creates a new workbook called Master
5. copies from all the cleaned files & pastes into the Master. Saves Master. Macro ends.

So now, I'm facing some probs with the formatting when I paste it into the Master. In the cleaned file, it is ok. But once it transfers, the formatting goes bonkers.

Eg: Original text: 5/13, 10/53, 8/37 (it's column size per cm)
After consolidation: May-13, October-53, August-37'

How to solve this when I've already included the codes (in previous reply) into my macro? All help much appreciated!! :)

rory
08-14-2007, 02:14 AM
Try changing the consolidation macro to this version, which will actually copy the cells (including formatting) rather than just using the values:


Sub Consolidation()
Dim wbk As Workbook, wbkMaster As Workbook
Dim wksSource As Worksheet, wksDest As Worksheet
Dim StrFile As String, strPath As String
Dim rngLastCell As Range
Dim lngRowCount As Long, lngTargRow As Long, lngCounter As Long
Dim varData
Dim strSlash As String
strSlash = Application.PathSeparator
' Note: to use fixed path, uncomment next line and change to whatever path you want
' strPath = "C:\Test"
' Allows you to pick a folder each time
strPath = GetFolder
If strPath = "" Then
MsgBox "You must choose a path!"
Exit Sub
End If
Application.ScreenUpdating = False
lngCounter = 1
If Right$(strPath, 1) <> strSlash Then strPath = strPath & strSlash
If Dir(strPath & "cleaned", vbDirectory) = "" Then MkDir strPath & "cleaned"
StrFile = Dir(strPath & "*.xls")
Set wbkMaster = Workbooks.Add
Set wksDest = wbkMaster.Worksheets(1)
wksDest.Name = "Data1"
lngTargRow = 2
Do Until StrFile = ""
If Not StrFile = ThisWorkbook.Name Then
Set wbk = Workbooks.Open(strPath & StrFile)
If CleanFile(StrFile) = True Then
' Assumes only one sheet
Set wksSource = wbk.Worksheets(1)
Set rngLastCell = LastCellInSheet(wksSource)
lngRowCount = rngLastCell.Row - 1
If lngTargRow + lngRowCount - 1 > 65536 Then
lngCounter = lngCounter + 1
Set wksDest = wbkMaster.Sheets.Add
wksDest.Name = "Data " & lngCounter
lngTargRow = 2
End If
With wksSource
.Range(.Cells(2, "A"), rngLastCell).Copy wksDest.Cells(lngTargRow, 1)
End With
lngTargRow = lngTargRow + lngRowCount
End If
wbk.SaveAs strPath & "cleaned" & strSlash & Replace$(wbk.Name, ".xls", "") _
& " clean " & Format$(Now, "yyyy-mm-dd hh.mm") & ".xls"
wbk.Close False
End If
StrFile = Dir
Loop
wbkMaster.SaveAs strPath & "Master.xls"
' wbkmaster.close
Application.ScreenUpdating = True
End Sub

Charlize
08-14-2007, 02:16 AM
Sub reformat_text()
'To get rid of that annoying green arrow
'The so called helpfull hint system of excel
'This one is for the date
Application.ErrorCheckingOptions.TextDate = False
'First do the formatting as text
With Worksheets(1)
.Columns("I:I").NumberFormat = "@"
'Then the pasting of a value that looks like a date
.Range("I2") = "05/13"
End With
'Set the helpsystem for the date back to true
Application.ErrorCheckingOptions.TextDate = True
End Sub
and add a line to format the destination workbook. Before the Do Until.

wksDest.Columns("I:I").NumberFormat = "@"

fwawergurl16
08-14-2007, 02:28 AM
Coolness Rory & Charlize!! Thank u very much for the help!! Appreciate it loads :D

Charlize
08-14-2007, 02:30 AM
Or you could remove the green arrows after all the pasting in the destination workbook

Sub remove_green_arrow_for_text_date()
Dim cell As Range
Application.ErrorCheckingOptions.TextDate = False
For Each cell In Range("I2:I" & Range("I" & Rows.Count).End(xlUp).Row)
cell.Value = cell.Value
Next cell
Application.ErrorCheckingOptions.TextDate = True
End Sub

fwawergurl16
08-15-2007, 07:18 PM
Thanks Charlize! Makes the sheet looks neater :P