Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change "With Worksheets" to "Activesheet"
Code works perfectly....
But plan on using this on various sheets. How do i change this line to "active sheet"? With Worksheets("sheet 1") Sub GetFirstFourLetters() Dim Col As Variant Dim X As Long Dim Z As Long Dim LastRow As Long With Worksheets("s.07") For Each Col In Array("A", "B") LastRow = .Cells(Rows.Count, Col).End(xlUp).Row For X = 1 To LastRow For Z = 1 To Len(.Cells(X, Col).Value) With .Cells(X, Col) If Mid$(.Value, Z, 1) Like "[A-Za-z]" Then .Offset(0, 2).Value = Mid$(.Value, Z, 4) Exit For End If End With Next Next Next End With End Sub Thanx |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change "With Worksheets" to "Activesheet"
With ActiveSheet
-- Gary''s Student - gsnu200796 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change "With Worksheets" to "Activesheet"
You said...
With Worksheets("sheet 1") but I am guessing you meant... With Worksheets("s.07") which is what is in the code. Anyway, change it to this... With ActiveSheet ActiveSheet is a automatically tracked object in VB referencing the active sheet in the workbook. Rick "J.W. Aldridge" wrote in message ... Code works perfectly.... But plan on using this on various sheets. How do i change this line to "active sheet"? With Worksheets("sheet 1") Sub GetFirstFourLetters() Dim Col As Variant Dim X As Long Dim Z As Long Dim LastRow As Long With Worksheets("s.07") For Each Col In Array("A", "B") LastRow = .Cells(Rows.Count, Col).End(xlUp).Row For X = 1 To LastRow For Z = 1 To Len(.Cells(X, Col).Value) With .Cells(X, Col) If Mid$(.Value, Z, 1) Like "[A-Za-z]" Then .Offset(0, 2).Value = Mid$(.Value, Z, 4) Exit For End If End With Next Next Next End With End Sub Thanx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
What are the reasons for ""ActiveSheet.Paste Link:=True"" failure | Excel Programming | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
use variable in Workbooks("book1").Worksheets("sheet1").Range("a1" | Excel Programming |