Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell by Cell Loop
I need help getting the syntax right for a loop...
This is what I am trying to accomplish: Start in A2, run spell check on that cell only. Go to the next cell in that row with data (if any, up to the last column) and run spell check on that cell only. Go to the next cell in that row with data (if any, up to the last last column) run spell check on that cell only...etc. Then move to Row 3 and do the same... Then Row 4...then 5...etc. Until we hit the last row. Can someone help me get the syntax right? I know this is probably a ways off, but this is where I left off. (As you can see..need a lot of help...it obviously doesn't work). Sub SpellCheck3() Dim LastRow As Long Dim LastCol As Long LastRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column Debug.Print LastRow, LastCol Set LastCell = ActiveSheet.Cells(LastRow, LastCol) x = 2 z = 1 Do Until x = LastRow Do Until z = LastCol Set mc = ActiveSheet.Cells(x, z) Application.Goto(ActiveSheet.Range(mc.convertformu la(formula:=mc, FromReferenceStyle:=R1C1 _ , ToReferenceStyle:=A1) Range(x.Address, z.Address).CheckSpelling SpellLang:=1033, AlwaysSuggest:=True z = z + 1 Loop z = 1 x = x + 1 Loop End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell by Cell Loop
This isn't pretty but it will do what you want. It will spell check each cell
column by column Option Explicit Sub SpellDown() Do ActiveCell.Offset(1, 0).Select ActiveCell.CheckSpelling SpellLang:=1033, _ AlwaysSuggest:=True On Error GoTo Right Loop Right: ActiveCell.Offset(0, 1).Select SpellUp End Sub Sub SpellUp() Do ActiveCell.Offset(-1, 0).Select ActiveCell.CheckSpelling SpellLang:=1033, _ AlwaysSuggest:=True On Error GoTo Right Loop Right: ActiveCell.Offset(0, 1).Select SpellDown End Sub "Josh O." wrote: I need help getting the syntax right for a loop... This is what I am trying to accomplish: Start in A2, run spell check on that cell only. Go to the next cell in that row with data (if any, up to the last column) and run spell check on that cell only. Go to the next cell in that row with data (if any, up to the last last column) run spell check on that cell only...etc. Then move to Row 3 and do the same... Then Row 4...then 5...etc. Until we hit the last row. Can someone help me get the syntax right? I know this is probably a ways off, but this is where I left off. (As you can see..need a lot of help...it obviously doesn't work). Sub SpellCheck3() Dim LastRow As Long Dim LastCol As Long LastRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column Debug.Print LastRow, LastCol Set LastCell = ActiveSheet.Cells(LastRow, LastCol) x = 2 z = 1 Do Until x = LastRow Do Until z = LastCol Set mc = ActiveSheet.Cells(x, z) Application.Goto(ActiveSheet.Range(mc.convertformu la(formula:=mc, FromReferenceStyle:=R1C1 _ , ToReferenceStyle:=A1) Range(x.Address, z.Address).CheckSpelling SpellLang:=1033, AlwaysSuggest:=True z = z + 1 Loop z = 1 x = x + 1 Loop End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell by Cell Loop
Great...I am one step closer.
Is it possible to have the spell check stop when it hits the lastrow with data and move to the next column (up to when it hits the Last Column with data in it)...or have it only check cells with data. "Office_Novice" wrote: This isn't pretty but it will do what you want. It will spell check each cell column by column Option Explicit Sub SpellDown() Do ActiveCell.Offset(1, 0).Select ActiveCell.CheckSpelling SpellLang:=1033, _ AlwaysSuggest:=True On Error GoTo Right Loop Right: ActiveCell.Offset(0, 1).Select SpellUp End Sub Sub SpellUp() Do ActiveCell.Offset(-1, 0).Select ActiveCell.CheckSpelling SpellLang:=1033, _ AlwaysSuggest:=True On Error GoTo Right Loop Right: ActiveCell.Offset(0, 1).Select SpellDown End Sub "Josh O." wrote: I need help getting the syntax right for a loop... This is what I am trying to accomplish: Start in A2, run spell check on that cell only. Go to the next cell in that row with data (if any, up to the last column) and run spell check on that cell only. Go to the next cell in that row with data (if any, up to the last last column) run spell check on that cell only...etc. Then move to Row 3 and do the same... Then Row 4...then 5...etc. Until we hit the last row. Can someone help me get the syntax right? I know this is probably a ways off, but this is where I left off. (As you can see..need a lot of help...it obviously doesn't work). Sub SpellCheck3() Dim LastRow As Long Dim LastCol As Long LastRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column Debug.Print LastRow, LastCol Set LastCell = ActiveSheet.Cells(LastRow, LastCol) x = 2 z = 1 Do Until x = LastRow Do Until z = LastCol Set mc = ActiveSheet.Cells(x, z) Application.Goto(ActiveSheet.Range(mc.convertformu la(formula:=mc, FromReferenceStyle:=R1C1 _ , ToReferenceStyle:=A1) Range(x.Address, z.Address).CheckSpelling SpellLang:=1033, AlwaysSuggest:=True z = z + 1 Loop z = 1 x = x + 1 Loop End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell by Cell Loop
I am sure its possible. i just dont know how.
"Josh O." wrote: Great...I am one step closer. Is it possible to have the spell check stop when it hits the lastrow with data and move to the next column (up to when it hits the Last Column with data in it)...or have it only check cells with data. "Office_Novice" wrote: This isn't pretty but it will do what you want. It will spell check each cell column by column Option Explicit Sub SpellDown() Do ActiveCell.Offset(1, 0).Select ActiveCell.CheckSpelling SpellLang:=1033, _ AlwaysSuggest:=True On Error GoTo Right Loop Right: ActiveCell.Offset(0, 1).Select SpellUp End Sub Sub SpellUp() Do ActiveCell.Offset(-1, 0).Select ActiveCell.CheckSpelling SpellLang:=1033, _ AlwaysSuggest:=True On Error GoTo Right Loop Right: ActiveCell.Offset(0, 1).Select SpellDown End Sub "Josh O." wrote: I need help getting the syntax right for a loop... This is what I am trying to accomplish: Start in A2, run spell check on that cell only. Go to the next cell in that row with data (if any, up to the last column) and run spell check on that cell only. Go to the next cell in that row with data (if any, up to the last last column) run spell check on that cell only...etc. Then move to Row 3 and do the same... Then Row 4...then 5...etc. Until we hit the last row. Can someone help me get the syntax right? I know this is probably a ways off, but this is where I left off. (As you can see..need a lot of help...it obviously doesn't work). Sub SpellCheck3() Dim LastRow As Long Dim LastCol As Long LastRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column Debug.Print LastRow, LastCol Set LastCell = ActiveSheet.Cells(LastRow, LastCol) x = 2 z = 1 Do Until x = LastRow Do Until z = LastCol Set mc = ActiveSheet.Cells(x, z) Application.Goto(ActiveSheet.Range(mc.convertformu la(formula:=mc, FromReferenceStyle:=R1C1 _ , ToReferenceStyle:=A1) Range(x.Address, z.Address).CheckSpelling SpellLang:=1033, AlwaysSuggest:=True z = z + 1 Loop z = 1 x = x + 1 Loop End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell by Cell Loop
Anyone else have a thought...
Thanks Office_Novice. "Office_Novice" wrote: This isn't pretty but it will do what you want. It will spell check each cell column by column Option Explicit Sub SpellDown() Do ActiveCell.Offset(1, 0).Select ActiveCell.CheckSpelling SpellLang:=1033, _ AlwaysSuggest:=True On Error GoTo Right Loop Right: ActiveCell.Offset(0, 1).Select SpellUp End Sub Sub SpellUp() Do ActiveCell.Offset(-1, 0).Select ActiveCell.CheckSpelling SpellLang:=1033, _ AlwaysSuggest:=True On Error GoTo Right Loop Right: ActiveCell.Offset(0, 1).Select SpellDown End Sub "Josh O." wrote: I need help getting the syntax right for a loop... This is what I am trying to accomplish: Start in A2, run spell check on that cell only. Go to the next cell in that row with data (if any, up to the last column) and run spell check on that cell only. Go to the next cell in that row with data (if any, up to the last last column) run spell check on that cell only...etc. Then move to Row 3 and do the same... Then Row 4...then 5...etc. Until we hit the last row. Can someone help me get the syntax right? I know this is probably a ways off, but this is where I left off. (As you can see..need a lot of help...it obviously doesn't work). Sub SpellCheck3() Dim LastRow As Long Dim LastCol As Long LastRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column Debug.Print LastRow, LastCol Set LastCell = ActiveSheet.Cells(LastRow, LastCol) x = 2 z = 1 Do Until x = LastRow Do Until z = LastCol Set mc = ActiveSheet.Cells(x, z) Application.Goto(ActiveSheet.Range(mc.convertformu la(formula:=mc, FromReferenceStyle:=R1C1 _ , ToReferenceStyle:=A1) Range(x.Address, z.Address).CheckSpelling SpellLang:=1033, AlwaysSuggest:=True z = z + 1 Loop z = 1 x = x + 1 Loop End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell by Cell Loop
Got it to work...here is the code I am using...
Sub SpellCheck() ActiveSheet.Unprotect Password:="" Dim LastRow As Long Dim LastCol As Long LastRow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row + 1 LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column + 1 Debug.Print LastRow, LastCol Set LastCell = ActiveSheet.Cells(LastRow, LastCol) x = 1 z = 1 Application.EnableCancelKey = xlDisabled Application.SpellingOptions.IgnoreMixedDigits = True Do Until x = LastRow Do Until z = LastRow Set mc = ActiveSheet.Cells(x, z) Application.Goto Reference:=mc, Scroll:=True ActiveCell.CheckSpelling SpellLang:=1033, AlwaysSuggest:=True z = z + 1 Loop z = 3 x = x + 1 Loop Range("A2").Select Application.EnableCancelKey = xlEnabled Application.ScreenUpdating = True ActiveSheet.Protect Password:="", DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowFormattingCells:=True, AllowSorting:=True, AllowFiltering:=True MsgBox Prompt:="SpellCheck has been completed!", Buttons:=vbOKOnly, Title:="SpellCheck" End Sub "Josh O." wrote: Anyone else have a thought... Thanks Office_Novice. "Office_Novice" wrote: This isn't pretty but it will do what you want. It will spell check each cell column by column Option Explicit Sub SpellDown() Do ActiveCell.Offset(1, 0).Select ActiveCell.CheckSpelling SpellLang:=1033, _ AlwaysSuggest:=True On Error GoTo Right Loop Right: ActiveCell.Offset(0, 1).Select SpellUp End Sub Sub SpellUp() Do ActiveCell.Offset(-1, 0).Select ActiveCell.CheckSpelling SpellLang:=1033, _ AlwaysSuggest:=True On Error GoTo Right Loop Right: ActiveCell.Offset(0, 1).Select SpellDown End Sub "Josh O." wrote: I need help getting the syntax right for a loop... This is what I am trying to accomplish: Start in A2, run spell check on that cell only. Go to the next cell in that row with data (if any, up to the last column) and run spell check on that cell only. Go to the next cell in that row with data (if any, up to the last last column) run spell check on that cell only...etc. Then move to Row 3 and do the same... Then Row 4...then 5...etc. Until we hit the last row. Can someone help me get the syntax right? I know this is probably a ways off, but this is where I left off. (As you can see..need a lot of help...it obviously doesn't work). Sub SpellCheck3() Dim LastRow As Long Dim LastCol As Long LastRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column Debug.Print LastRow, LastCol Set LastCell = ActiveSheet.Cells(LastRow, LastCol) x = 2 z = 1 Do Until x = LastRow Do Until z = LastCol Set mc = ActiveSheet.Cells(x, z) Application.Goto(ActiveSheet.Range(mc.convertformu la(formula:=mc, FromReferenceStyle:=R1C1 _ , ToReferenceStyle:=A1) Range(x.Address, z.Address).CheckSpelling SpellLang:=1033, AlwaysSuggest:=True z = z + 1 Loop z = 1 x = x + 1 Loop End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Two Cell Loop | Excel Programming | |||
VBA Loop until certain cell! | Excel Programming | |||
Loop to add +1 in cell? | Excel Programming | |||
Loop until cell is empty | Excel Programming | |||
loop through a cell. | Excel Programming |