Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA help for functions
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA help for functions
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 "JC" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA help for functions
Sub pt1b()
On Error Resume Next For i = 1 To Sheets.Count With Sheets(i).Columns(1).SpecialCells(xlCellTypeConsta nts, xlTextValues) ..Value = "a" & .Value ..Characters(1, 1).Font.Name = "Marlett" End With Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Is this idea quicker? Sub pt1a() On Error Resume Next For i = 1 To Sheets.Count With Sheets(i).Columns(1).SpecialCells(xlCellTypeConsta nts, xlTextValues) .Value = "X" & .Value End With Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "JC" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA help for functions
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--- -- RyGuy "Bernie Deitrick" wrote: 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 "JC" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA help for functions
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. "ryguy7272" wrote: 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--- -- RyGuy "Bernie Deitrick" wrote: 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 "JC" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula/functions for average and if functions | Excel Worksheet Functions | |||
XL2003 FILTER FUNCTIONS VS. XL2007 FILTER FUNCTIONS | Excel Worksheet Functions | |||
efficiency: database functions vs. math functions vs. array formula | Excel Discussion (Misc queries) | |||
Looking for a site with functions that substitute the ATP functions | Excel Worksheet Functions | |||
Nesting functions in the functions dialog box | Excel Worksheet Functions |