SETTING RANGE IN EXCEL 2

[butchkmd]

I have an application in excel in which we would like to use one cell per letter much like a form. I have a macro in a modual evaluating the len(activecell.value)
if > 0 I want it to move to the next unlocked cell.


Dim varCol As Variant
Dim varRow As Variant
Dim varCell As Variant '
' autotab Macro
' Macro recorded 4/4/2002 by mtc
'

varCell = ActiveCell.Value
varCol = ActiveCell.Column
varRow = ActiveCell.Row
'varRow = cell("row", ActiveCell)
'Select Case ActiveCell.Address

If Len(varCell) > 0 Then
Range(varCol + 1 & varRow).Select
End If

End Sub
*******************************************************
the code above blows up when it gets to the range statment
some type of 'global' error.

Any sugestions?

Matt

 

[SkipVought]

Hi,

I think that i gave you a function that splits text up into one character per cell.

In this case, your problem may be that your sheet is Protected. At the beginning of your procedure, Unprotect the sheet and at the end Protect it.

Certain Range functions do not work on protected sheets.

Hope this helps ;-0 Skip,
metzgsk@voughtaircraft.com

 

[xlbo]

You can't use the Range() select method with these variables
varCol is an integer, as is varRow so if you were in A1 to start with, varCol would = 1 and varRow would = 1
Range(varCol + 1 & varRow).Select would evaluate to range(21).select........which won't work

You need to use the "Cells" method

Range(cells(varRow,varCol+1),cells(varRow,varCol+1)).select

Note, you must use 2 arguments for this as it is usually used to select a range with more than 1 cell.
Syntax:
Range(cells(r,c),cells(r,c)).select (or activate or whatever)

HTH
Geoff

 

[butchkmd]

Fantastic you are super great it works fine
I am posting another string RE: is it possible to activate a macro by any keystroke and not have to hit enter to fire the worsheet change event?

Thanks again

 

[DaleWatson123321]

Matt,

Given that you've been a Tek-Tips member for almost 5 months, I believe it's time someone pointed out the need for you to "say thanks" in the "proper" way.

The "proper" method - of saying thanks and showing recognition for the time a contributor takes out of his/her BUSY schedule to help you - involves "issuing a STAR".

Issuing a “STAR” is a simple matter of clicking on the "Click here to mark this post as a helpful or expert post!" - located in the lower-left-corner of the contributor's posting.

Please do NOT issue a star for this "reminder" - I want to "earn" stars in the normal way. Thanks

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca