VBA help for functions

[Guest]

VBA help
to add tickmark to range that has data only trying this code
'Format the column to Add Ticks
Dim X As Variant

If WorksheetFunction.CountA(Range("E4:E2300")) > 0 Then
If Len(X) > 0 Then 'Find cells with data
X.FormulaR1C1 = Chr(39) & X.Text '39 is code for tick
Else
X.FormulaR1C1 = "" 'If empty do not put tick
End If
End If

end sub

This works but it takes too long to go through the range and when I have
19 sheets to have 1hr just to format too long

' Format the column to Add Ticks
' Dim Z As Variant, X As Variant
' 'Range("E4:E" & Lastrow).Select

' Range("E4").Select
' Range(Selection, Selection.End(xlDown)).Select

' Z = Selection.Address 'get the address
' For Each X In ActiveSheet.Range(Z) 'Do while
' If Len(X) > 0 Then 'Find cells with data
' X.FormulaR1C1 = Chr(39) & X.Text '39 is code for tick
' Else
' X.FormulaR1C1 = "" 'If empty do not put tick
'End If
'Next

End Sub

 

[Don Guillett]

Is this idea quicker?

Sub pt1a()
On Error Resume Next
For i = 1 To Sheets.Count
With Sheets(i).Columns(1).SpecialCells(xlCellTypeConstants, xlTextValues)
.Value = "X" & .Value
End With
Next i
End Sub

 

[Bernie Deitrick]

JC,

39 is not the code for a tick - it's the code for a single quote, which won't show - so all you are
really doing is converting all the filled cells to values:

Sub TryNow()
Dim myA As String
Dim mySht As Worksheet

myA = "E4:E2300"

For Each mySht In Worksheets
mySht.Range(myA).Value = mySht.Range(myA).Value
Next mySht

End Sub

HTH,
Bernie
MS Excel MVP

 

[Don Guillett]

Sub pt1b()
On Error Resume Next
For i = 1 To Sheets.Count
With Sheets(i).Columns(1).SpecialCells(xlCellTypeConstants, xlTextValues)
..Value = "a" & .Value
..Characters(1, 1).Font.Name = "Marlett"
End With
Next i
End Sub

 

[Guest]

That is AWESOME Bernie!

JC, I am curious...did your data originally come from Outlook? I just
finished a project which required me to export thousands of contacts from
Outlook to Excel. All contacts came into Excel with ‘hard returns’ in the
address areas. I put a post on this DG a short time ago, and someone gave me
the macro below. The macro will remove all ‘hard returns’ from your SS.

Just before running this macro, select the small square between A and 1 in
the upper left hand corner of your SS.

Sub Remove_CR_LF()
With Selection
..Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
..Replace What:=Chr(13) & Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
..Replace What:=Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End With
End Sub


Regards,
Ryan---

 

[Guest]

I have come to find out that the data actually comes from another excel
workbook
that doesn't have access to Hyperion Essbase which is what is making me
reformat the data around with hidden ' and 0 for items that don't have the
leading zero so that essbase will recognize the data in the column.