Range.Value doesn't work in hidden columns

Can anybody help me with this - I've been all over Google trying to
figure it out.

The following code:

With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("dataWeekID") = Range("H" & r).Value
.Fields("Month") = Range("I" & r).Value
.Fields("SBU") = Range("J" & r).Value
.Fields("PC") = Range("K" & r).Value
.Fields("Rev") = CLng(Range("E" & r).Value)
.Fields("Mgn") = CLng(Range("F" & r).Value)
.Fields("Comments") = Range("G" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With

Works fine if the columns are visible. However, when I hide the
columns (H,I,J & K) and debug, the Value property comes up empty.
Anybody know a way around this?

Thanks!

Jeff

this demo from the immediate window shows there is no problem with using that
construct when the column is hidden

r = 10
Range("K" & r).Value = 100
Range("K:K").EntireColumn.Hidden = True
? Range("K" & r).Value
100



--
Regards,
Tom Ogilvy



"andraandjeff@gmail.com" wrote:

> Can anybody help me with this - I've been all over Google trying to
> figure it out.
>
> The following code:
>
> With rs
> .AddNew ' create a new record
> ' add values to each field in the record
> .Fields("dataWeekID") = Range("H" & r).Value
> .Fields("Month") = Range("I" & r).Value
> .Fields("SBU") = Range("J" & r).Value
> .Fields("PC") = Range("K" & r).Value
> .Fields("Rev") = CLng(Range("E" & r).Value)
> .Fields("Mgn") = CLng(Range("F" & r).Value)
> .Fields("Comments") = Range("G" & r).Value
> ' add more fields if necessary...
> .Update ' stores the new record
> End With
>
> Works fine if the columns are visible. However, when I hide the
> columns (H,I,J & K) and debug, the Value property comes up empty.
> Anybody know a way around this?
>
> Thanks!
>
> Jeff
>
>

Thanks Tom - you're right. I assumed something that wasn't the case.
I went back and found an unrelated error in my code.

cheers,

Jeff


Tom Ogilvy wrote:
> this demo from the immediate window shows there is no problem with using that
> construct when the column is hidden
>
> r = 10
> Range("K" & r).Value = 100
> Range("K:K").EntireColumn.Hidden = True
> ? Range("K" & r).Value
> 100
>
>
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "andraandjeff@gmail.com" wrote:
>
> > Can anybody help me with this - I've been all over Google trying to
> > figure it out.
> >
> > The following code:
> >
> > With rs
> > .AddNew ' create a new record
> > ' add values to each field in the record
> > .Fields("dataWeekID") = Range("H" & r).Value
> > .Fields("Month") = Range("I" & r).Value
> > .Fields("SBU") = Range("J" & r).Value
> > .Fields("PC") = Range("K" & r).Value
> > .Fields("Rev") = CLng(Range("E" & r).Value)
> > .Fields("Mgn") = CLng(Range("F" & r).Value)
> > .Fields("Comments") = Range("G" & r).Value
> > ' add more fields if necessary...
> > .Update ' stores the new record
> > End With
> >
> > Works fine if the columns are visible. However, when I hide the
> > columns (H,I,J & K) and debug, the Value property comes up empty.
> > Anybody know a way around this?
> >
> > Thanks!
> >
> > Jeff
> >
> >