DEAR ALL

I intended to use the result of RCOUNT variable to various other
procedures.

So I have named a cell by name REF_NUMBR.

Flg is the vb code:
Set COL_F = Range("F5.F20")
RCOUNT = Application.CountA(COL_F)
100 Names("REF_NUMBR").Value = RCOUNT

but at line 100 it gives error saying application-defined or object-
defined error.
Then I made it as activeworkbook.Names("REF_NUMBR").Value = RCOUNT
and it accepts.

Instead of the above if i use Range("REF_NUMBR").Value = RCOUNT
it takes the value.

Earlier i was using activesheet.range("a1").value = RCOUNT and was
referring to this range.

Pls advice how to use the line 100 correctly using the 'NAMES' and
shortest code.

On Feb 16, 8:56 am, "CAPTGNVR" <captg...@gmail.com> wrote:
> DEAR ALL
>
> I intended to use the result of RCOUNT variable to various other
> procedures.
>
> So I have named a cell by name REF_NUMBR.
>
> Flg is the vb code:
> Set COL_F = Range("F5.F20")
> RCOUNT = Application.CountA(COL_F)
> 100 Names("REF_NUMBR").Value = RCOUNT
>
> but at line 100 it gives error saying application-defined or object-
> defined error.
> Then I made it as activeworkbook.Names("REF_NUMBR").Value = RCOUNT
> and it accepts.
>
> Instead of the above if i use Range("REF_NUMBR").Value = RCOUNT
> it takes the value.
>
> Earlier i was using activesheet.range("a1").value = RCOUNT and was
> referring to this range.
>
> Pls advice how to use the line 100 correctly using the 'NAMES' and
> shortest code.

Line numbers in this case are really labels. to use it put a colon
after it.

100: Names("REF_NUMBR").Value = RCOUNT

But this line defines your named range "REF_NUMBR" = COUNTA
In other words, your range doesn't mean anything. Were you trying to
set a cell value to COUNTA?

Names("REF_NUMBR").referstorange.Value = RCOUNT

But I like:
worksheets("Somesheet").range("ref_number").value = rcount



CAPTGNVR wrote:
>
> DEAR ALL
>
> I intended to use the result of RCOUNT variable to various other
> procedures.
>
> So I have named a cell by name REF_NUMBR.
>
> Flg is the vb code:
> Set COL_F = Range("F5.F20")
> RCOUNT = Application.CountA(COL_F)
> 100 Names("REF_NUMBR").Value = RCOUNT
>
> but at line 100 it gives error saying application-defined or object-
> defined error.
> Then I made it as activeworkbook.Names("REF_NUMBR").Value = RCOUNT
> and it accepts.
>
> Instead of the above if i use Range("REF_NUMBR").Value = RCOUNT
> it takes the value.
>
> Earlier i was using activesheet.range("a1").value = RCOUNT and was
> referring to this range.
>
> Pls advice how to use the line 100 correctly using the 'NAMES' and
> shortest code.

--

Dave Peterson

Maybe I'm missing something. Try

Sub countcolf()
range("REF_NUMBR").value= Application.CountA(range("f5:f20"))
End Sub

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"CAPTGNVR" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> DEAR ALL
>
> I intended to use the result of RCOUNT variable to various other
> procedures.
>
> So I have named a cell by name REF_NUMBR.
>
> Flg is the vb code:
> Set COL_F = Range("F5.F20")
> RCOUNT = Application.CountA(COL_F)
> 100 Names("REF_NUMBR").Value = RCOUNT
>
> but at line 100 it gives error saying application-defined or object-
> defined error.
> Then I made it as activeworkbook.Names("REF_NUMBR").Value = RCOUNT
> and it accepts.
>
> Instead of the above if i use Range("REF_NUMBR").Value = RCOUNT
> it takes the value.
>
> Earlier i was using activesheet.range("a1").value = RCOUNT and was
> referring to this range.
>
> Pls advice how to use the line 100 correctly using the 'NAMES' and
> shortest code.
>

On Feb 16, 11:48 pm, "Don Guillett" <dguille...@austin.rr.com> wrote:
> Maybe I'm missing something. Try
>
> Sub countcolf()
> range("REF_NUMBR").value= Application.CountA(range("f5:f20"))
> End Sub
>
> --
> Don Guillett
> SalesAid Software
> dguille...@austin.rr.com"CAPTGNVR" <captg...@gmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
> > DEAR ALL
>
> > I intended to use the result of RCOUNT variable to various other
> > procedures.
>
> > So I have named a cell by name REF_NUMBR.
>
> > Flg is the vb code:
> > Set COL_F = Range("F5.F20")
> > RCOUNT = Application.CountA(COL_F)
> > 100 Names("REF_NUMBR").Value = RCOUNT
>
> > but at line 100 it gives error saying application-defined or object-
> > defined error.
> > Then I made it as activeworkbook.Names("REF_NUMBR").Value = RCOUNT
> > and it accepts.
>
> > Instead of the above if i use Range("REF_NUMBR").Value = RCOUNT
> > it takes the value.
>
> > Earlier i was using activesheet.range("a1").value = RCOUNT and was
> > referring to this range.
>
> > Pls advice how to use the line 100 correctly using the 'NAMES' and
> > shortest code.

Thnk u Don. Actually i find it difficult to put it down in writing in
this forum. Luckily i got the answer from Mr. Dave. I wanted to know
how to use the Name property as i was getting error when i use. Now
this thread i can consider closed once i get from Dave how to set a
variable to the whole line defining the range.

On Feb 16, 11:43 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> Names("REF_NUMBR").referstorange.Value = RCOUNT
>
> But I like:
> worksheets("Somesheet").range("ref_number").value = rcount
>
>
>
> CAPTGNVR wrote:
>
> > DEAR ALL
>
> > I intended to use the result of RCOUNT variable to various other
> > procedures.
>
> > So I have named a cell by name REF_NUMBR.
>
> > Flg is the vb code:
> > Set COL_F = Range("F5.F20")
> > RCOUNT = Application.CountA(COL_F)
> > 100 Names("REF_NUMBR").Value = RCOUNT
>
> > but at line 100 it gives error saying application-defined or object-
> > defined error.
> > Then I made it as activeworkbook.Names("REF_NUMBR").Value = RCOUNT
> > and it accepts.
>
> > Instead of the above if i use Range("REF_NUMBR").Value = RCOUNT
> > it takes the value.
>
> > Earlier i was using activesheet.range("a1").value = RCOUNT and was
> > referring to this range.
>
> > Pls advice how to use the line 100 correctly using the 'NAMES' and
> > shortest code.
>
> --
>
> Dave Peterson

Thnk u Dave. Frankly on this ship I have learnt so many things thro
this forum thanks to u. I got what i wanted. How to use the property
names. It worked. Can u take me to the next step?? Instead of
writing the whole thing like ---
worksheets("Somesheet").range("ref_number").value = rcount--- can u
suggest how to set this as public and set a variable so that i can
reference this without having the need to type the whole thing.

On Feb 16, 11:21 pm, "okrob" <merobwhoare...@yahoo.com> wrote:
> On Feb 16, 8:56 am, "CAPTGNVR" <captg...@gmail.com> wrote:
>
>
>
> > DEAR ALL
>
> > I intended to use the result of RCOUNT variable to various other
> > procedures.
>
> > So I have named a cell by name REF_NUMBR.
>
> > Flg is the vb code:
> > Set COL_F = Range("F5.F20")
> > RCOUNT = Application.CountA(COL_F)
> > 100 Names("REF_NUMBR").Value = RCOUNT
>
> > but at line 100 it gives error saying application-defined or object-
> > defined error.
> > Then I made it as activeworkbook.Names("REF_NUMBR").Value = RCOUNT
> > and it accepts.
>
> > Instead of the above if i use Range("REF_NUMBR").Value = RCOUNT
> > it takes the value.
>
> > Earlier i was using activesheet.range("a1").value = RCOUNT and was
> > referring to this range.
>
> > Pls advice how to use the line 100 correctly using the 'NAMES' and
> > shortest code.
>
> Line numbers in this case are really labels. to use it put a colon
> after it.
>
> 100: Names("REF_NUMBR").Value = RCOUNT
>
> But this line defines your named range "REF_NUMBR" = COUNTA
> In other words, your range doesn't mean anything. Were you trying to
> set a cell value to COUNTA?

thnk u OKROB. But the line number i am not using it in the VB. I
missed to tell that i used here in this just to refer the line.

Dim myImportantCell as range
'or outside the procedure
'Public myImportantCell as range
....
set myimportantcell = worksheets("Somesheet").range("ref_number")
....
myimportantcell.value = rcount

You'll want to use a shorter, more meaningful name for that cell.

CAPTGNVR wrote:
>
> On Feb 16, 11:43 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > Names("REF_NUMBR").referstorange.Value = RCOUNT
> >
> > But I like:
> > worksheets("Somesheet").range("ref_number").value = rcount
> >
> >
> >
> > CAPTGNVR wrote:
> >
> > > DEAR ALL
> >
> > > I intended to use the result of RCOUNT variable to various other
> > > procedures.
> >
> > > So I have named a cell by name REF_NUMBR.
> >
> > > Flg is the vb code:
> > > Set COL_F = Range("F5.F20")
> > > RCOUNT = Application.CountA(COL_F)
> > > 100 Names("REF_NUMBR").Value = RCOUNT
> >
> > > but at line 100 it gives error saying application-defined or object-
> > > defined error.
> > > Then I made it as activeworkbook.Names("REF_NUMBR").Value = RCOUNT
> > > and it accepts.
> >
> > > Instead of the above if i use Range("REF_NUMBR").Value = RCOUNT
> > > it takes the value.
> >
> > > Earlier i was using activesheet.range("a1").value = RCOUNT and was
> > > referring to this range.
> >
> > > Pls advice how to use the line 100 correctly using the 'NAMES' and
> > > shortest code.
> >
> > --
> >
> > Dave Peterson
>
> Thnk u Dave. Frankly on this ship I have learnt so many things thro
> this forum thanks to u. I got what i wanted. How to use the property
> names. It worked. Can u take me to the next step?? Instead of
> writing the whole thing like ---
> worksheets("Somesheet").range("ref_number").value = rcount--- can u
> suggest how to set this as public and set a variable so that i can
> reference this without having the need to type the whole thing.

--

Dave Peterson

On Feb 17, 1:02 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> Dim myImportantCell as range
> 'or outside the procedure
> 'Public myImportantCell as range
> ...
> set myimportantcell = worksheets("Somesheet").range("ref_number")
> ...
> myimportantcell.value = rcount
>
> You'll want to use a shorter, more meaningful name for that cell.
>
>
>
> CAPTGNVR wrote:
>
> > On Feb 16, 11:43 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > Names("REF_NUMBR").referstorange.Value = RCOUNT
>
> > > But I like:
> > > worksheets("Somesheet").range("ref_number").value = rcount
>
> > > CAPTGNVR wrote:
>
> > > > DEAR ALL
>
> > > > I intended to use the result of RCOUNT variable to various other
> > > > procedures.
>
> > > > So I have named a cell by name REF_NUMBR.
>
> > > > Flg is the vb code:
> > > > Set COL_F = Range("F5.F20")
> > > > RCOUNT = Application.CountA(COL_F)
> > > > 100 Names("REF_NUMBR").Value = RCOUNT
>
> > > > but at line 100 it gives error saying application-defined or object-
> > > > defined error.
> > > > Then I made it as activeworkbook.Names("REF_NUMBR").Value = RCOUNT
> > > > and it accepts.
>
> > > > Instead of the above if i use Range("REF_NUMBR").Value = RCOUNT
> > > > it takes the value.
>
> > > > Earlier i was using activesheet.range("a1").value = RCOUNT and was
> > > > referring to this range.
>
> > > > Pls advice how to use the line 100 correctly using the 'NAMES' and
> > > > shortest code.
>
> > > --
>
> > > Dave Peterson
>
> > Thnk u Dave. Frankly on this ship I have learnt so many things thro
> > this forum thanks to u. I got what i wanted. How to use the property
> > names. It worked. Can u take me to the next step?? Instead of
> > writing the whole thing like ---
> > worksheets("Somesheet").range("ref_number").value = rcount--- can u
> > suggest how to set this as public and set a variable so that i can
> > reference this without having the need to type the whole thing.
>
> --
>
> Dave Peterson

Thank u v/much Dave. Custom made and clearly ustood how to do it.