Cell by Cell Loop

[Josh O.]

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.convertformula(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

 

[Office_Novice]

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.]

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]

I am sure its possible. i just dont know how.

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.

 

[Josh O.]

Anyone else have a thought...

Thanks Office_Novice.

 

[Josh O.]

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