| Home | Forums | Reviews | Articles | Register |
 |
| Thread Tools | Rate Thread |
|
Guest
Posts: n/a
|
Hello from Steved
Is it possible please to change Range("D5  500")) to say Range("AA5:AA500"))I've got different columns so I would like to please a Dailog box to come up when I press the Icon on my Ribbon to ask what column is reqired, for example Column B Sub Test() Dim myS As String Dim myC As Range For Each myC In Intersect(ActiveSheet.UsedRange, Range("D5 500"))myS = myC.Value myC.NumberFormat = "@" myC.Value = myS If Len(myC.Value) < 4 And myC.Value > "" Then Do Until Len(myC.Value) = 4 myC.Value = "0" & myC.Value Loop End If With myC.Characters(Start:=4, Length:=1).Font .FontStyle = "Bold" .Underline = xlUnderlineStyleSingle .COLOR = 255 End With Next myC End Sub Thankyou. |
|
|
|
|||
|
|
| |
|
Guest
Posts: n/a
|
hi
try adding this after your Dim's... Dim myR As String myR = InputBox("Enter a range") change this line from Range("D5"D500") to Range(myR) For Each myC In Intersect(ActiveSheet.UsedRange, Range(myR)) regards FSt1 "Steved" wrote: > Hello from Steved > > Is it possible please to change Range("D5 500")) to say Range("AA5:AA500"))> > I've got different columns so I would like to please a Dailog box to come up > when I press the Icon on my Ribbon to ask what column is reqired, for example > Column B > > Sub Test() > Dim myS As String > Dim myC As Range > > For Each myC In Intersect(ActiveSheet.UsedRange, Range("D5 500"))> myS = myC.Value > myC.NumberFormat = "@" > myC.Value = myS > If Len(myC.Value) < 4 And myC.Value > "" Then > Do Until Len(myC.Value) = 4 > myC.Value = "0" & myC.Value > Loop > End If > With myC.Characters(Start:=4, Length:=1).Font > .FontStyle = "Bold" > .Underline = xlUnderlineStyleSingle > .COLOR = 255 > End With > Next myC > End Sub > > Thankyou. |
|
|
|
|
|||
|
Guest
Posts: n/a
|
Option Explicit
Sub Test() Dim myS As String Dim myC As Range Dim myRng As Range Dim myRngToInspect As Range Set myRng = Nothing On Error Resume Next Set myRng = Application.InputBox(Prompt:="Select a column", _ Type:=8).Cells(1) On Error GoTo 0 If myRng Is Nothing Then 'user hit cancel Exit Sub '??? End If Set myRngToInspect = Intersect(myRng.Parent.UsedRange, _ myRng.EntireColumn, _ myRng.Parent.Range("5:500")) If myRngToInspect Is Nothing Then MsgBox "nothing to work on!" Exit Sub End If For Each myC In myRngToInspect.Cells myC.NumberFormat = "@" If IsEmpty(myC) Then 'skip it Else If IsNumeric(myC.Value) Then 'only the numbers < 10000???? If myC.Value < 10000 Then myC.Value = Format(myC.Value, "0000") End If End If End If With myC.Characters(Start:=4, Length:=1).Font .FontStyle = "Bold" .Underline = xlUnderlineStyleSingle .Color = 255 End With Next myC End Sub Steved wrote: > > Hello from Steved > > Is it possible please to change Range("D5 500")) to say Range("AA5:AA500"))> > I've got different columns so I would like to please a Dailog box to come up > when I press the Icon on my Ribbon to ask what column is reqired, for example > Column B > > Sub Test() > Dim myS As String > Dim myC As Range > > For Each myC In Intersect(ActiveSheet.UsedRange, Range("D5 500"))> myS = myC.Value > myC.NumberFormat = "@" > myC.Value = myS > If Len(myC.Value) < 4 And myC.Value > "" Then > Do Until Len(myC.Value) = 4 > myC.Value = "0" & myC.Value > Loop > End If > With myC.Characters(Start:=4, Length:=1).Font > .FontStyle = "Bold" > .Underline = xlUnderlineStyleSingle > .COLOR = 255 > End With > Next myC > End Sub > > Thankyou. -- Dave Peterson |
|
|
|
|
|||
|
Guest
Posts: n/a
|
Thankyou very much
Best Wishes for the Season Steved "FSt1" wrote: > hi > try adding this after your Dim's... > Dim myR As String > myR = InputBox("Enter a range") > > change this line from Range("D5"D500") to Range(myR) > For Each myC In Intersect(ActiveSheet.UsedRange, Range(myR)) > > regards > FSt1 > > > "Steved" wrote: > > > Hello from Steved > > > > Is it possible please to change Range("D5 500")) to say Range("AA5:AA500"))> > > > I've got different columns so I would like to please a Dailog box to come up > > when I press the Icon on my Ribbon to ask what column is reqired, for example > > Column B > > > > Sub Test() > > Dim myS As String > > Dim myC As Range > > > > For Each myC In Intersect(ActiveSheet.UsedRange, Range("D5 500"))> > myS = myC.Value > > myC.NumberFormat = "@" > > myC.Value = myS > > If Len(myC.Value) < 4 And myC.Value > "" Then > > Do Until Len(myC.Value) = 4 > > myC.Value = "0" & myC.Value > > Loop > > End If > > With myC.Characters(Start:=4, Length:=1).Font > > .FontStyle = "Bold" > > .Underline = xlUnderlineStyleSingle > > .COLOR = 255 > > End With > > Next myC > > End Sub > > > > Thankyou. |
|
|
|
|
|||
|
Guest
Posts: n/a
|
your's is a lot better than mine.
Regards FSt1 "Steved" wrote: > Hello from Steved > > Is it possible please to change Range("D5 500")) to say Range("AA5:AA500"))> > I've got different columns so I would like to please a Dailog box to come up > when I press the Icon on my Ribbon to ask what column is reqired, for example > Column B > > Sub Test() > Dim myS As String > Dim myC As Range > > For Each myC In Intersect(ActiveSheet.UsedRange, Range("D5 500"))> myS = myC.Value > myC.NumberFormat = "@" > myC.Value = myS > If Len(myC.Value) < 4 And myC.Value > "" Then > Do Until Len(myC.Value) = 4 > myC.Value = "0" & myC.Value > Loop > End If > With myC.Characters(Start:=4, Length:=1).Font > .FontStyle = "Bold" > .Underline = xlUnderlineStyleSingle > .COLOR = 255 > End With > Next myC > End Sub > > Thankyou. |
|
|
|
|
|||
|
Guest
Posts: n/a
|
Thankyou
Best wishes for the Season Steved "Dave Peterson" wrote: > Option Explicit > Sub Test() > Dim myS As String > Dim myC As Range > Dim myRng As Range > Dim myRngToInspect As Range > > Set myRng = Nothing > On Error Resume Next > Set myRng = Application.InputBox(Prompt:="Select a column", _ > Type:=8).Cells(1) > On Error GoTo 0 > > If myRng Is Nothing Then > 'user hit cancel > Exit Sub '??? > End If > > Set myRngToInspect = Intersect(myRng.Parent.UsedRange, _ > myRng.EntireColumn, _ > myRng.Parent.Range("5:500")) > > If myRngToInspect Is Nothing Then > MsgBox "nothing to work on!" > Exit Sub > End If > > For Each myC In myRngToInspect.Cells > myC.NumberFormat = "@" > > If IsEmpty(myC) Then > 'skip it > Else > If IsNumeric(myC.Value) Then > 'only the numbers < 10000???? > If myC.Value < 10000 Then > myC.Value = Format(myC.Value, "0000") > End If > End If > End If > > With myC.Characters(Start:=4, Length:=1).Font > .FontStyle = "Bold" > .Underline = xlUnderlineStyleSingle > .Color = 255 > End With > Next myC > End Sub > > Steved wrote: > > > > Hello from Steved > > > > Is it possible please to change Range("D5 500")) to say Range("AA5:AA500"))> > > > I've got different columns so I would like to please a Dailog box to come up > > when I press the Icon on my Ribbon to ask what column is reqired, for example > > Column B > > > > Sub Test() > > Dim myS As String > > Dim myC As Range > > > > For Each myC In Intersect(ActiveSheet.UsedRange, Range("D5 500"))> > myS = myC.Value > > myC.NumberFormat = "@" > > myC.Value = myS > > If Len(myC.Value) < 4 And myC.Value > "" Then > > Do Until Len(myC.Value) = 4 > > myC.Value = "0" & myC.Value > > Loop > > End If > > With myC.Characters(Start:=4, Length:=1).Font > > .FontStyle = "Bold" > > .Underline = xlUnderlineStyleSingle > > .COLOR = 255 > > End With > > Next myC > > End Sub > > > > Thankyou. > > -- > > Dave Peterson > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
Thankyou
Best Wishes for the Season Steved "Dave Peterson" wrote: > Option Explicit > Sub Test() > Dim myS As String > Dim myC As Range > Dim myRng As Range > Dim myRngToInspect As Range > > Set myRng = Nothing > On Error Resume Next > Set myRng = Application.InputBox(Prompt:="Select a column", _ > Type:=8).Cells(1) > On Error GoTo 0 > > If myRng Is Nothing Then > 'user hit cancel > Exit Sub '??? > End If > > Set myRngToInspect = Intersect(myRng.Parent.UsedRange, _ > myRng.EntireColumn, _ > myRng.Parent.Range("5:500")) > > If myRngToInspect Is Nothing Then > MsgBox "nothing to work on!" > Exit Sub > End If > > For Each myC In myRngToInspect.Cells > myC.NumberFormat = "@" > > If IsEmpty(myC) Then > 'skip it > Else > If IsNumeric(myC.Value) Then > 'only the numbers < 10000???? > If myC.Value < 10000 Then > myC.Value = Format(myC.Value, "0000") > End If > End If > End If > > With myC.Characters(Start:=4, Length:=1).Font > .FontStyle = "Bold" > .Underline = xlUnderlineStyleSingle > .Color = 255 > End With > Next myC > End Sub > > Steved wrote: > > > > Hello from Steved > > > > Is it possible please to change Range("D5 500")) to say Range("AA5:AA500"))> > > > I've got different columns so I would like to please a Dailog box to come up > > when I press the Icon on my Ribbon to ask what column is reqired, for example > > Column B > > > > Sub Test() > > Dim myS As String > > Dim myC As Range > > > > For Each myC In Intersect(ActiveSheet.UsedRange, Range("D5 500"))> > myS = myC.Value > > myC.NumberFormat = "@" > > myC.Value = myS > > If Len(myC.Value) < 4 And myC.Value > "" Then > > Do Until Len(myC.Value) = 4 > > myC.Value = "0" & myC.Value > > Loop > > End If > > With myC.Characters(Start:=4, Length:=1).Font > > .FontStyle = "Bold" > > .Underline = xlUnderlineStyleSingle > > .COLOR = 255 > > End With > > Next myC > > End Sub > > > > Thankyou. > > -- > > Dave Peterson > |
|
|
|
|
|||
|
|
| |
|
| Thread Tools | |
| Rate This Thread | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Unhide Columns dialog box | Stephen Lynch | Microsoft Access VBA Modules | 0 | 24th Sep 2008 03:05 PM |
| Multiple wrksheets same columns globally change order of columns? | Change order of columns multiple wkshts | Microsoft Excel Programming | 1 | 11th Sep 2008 06:04 PM |
| how to put three columns into one dialog box? | =?Utf-8?B?c2NvcnBpbw==?= | Microsoft Excel Worksheet Functions | 1 | 12th Sep 2007 07:42 PM |
| How to permanently change Dialog Caption while in the Dialog | =?Utf-8?B?TWlrZVp6?= | Microsoft Excel Programming | 2 | 11th Jul 2006 04:53 PM |
| How to permanently change Dialog Caption while in the Dialog | =?Utf-8?B?TWlrZVp6?= | Microsoft Excel Programming | 0 | 11th Jul 2006 04:14 PM |
