Re: Using a variable in a formula by Fred
Fred
Mon May 12 11:36:02 CDT 2008
Yes, it is working. Thank you for your help.
Fred
"Rick Rothstein (MVP - VB)" wrote:
> Okay, then the code I gave you is working... I wasn't completely sure what
> you are saying in your last posting. As for the quote marks... yes, the
> quote marks, as I listed them, are required by syntax... you can't remove or
> modify them as they delineate the non-changing portions of your text.
>
> Rick
>
>
> "Fred" <Fred@discussions.microsoft.com> wrote in message
> news:EB5D8E9E-DF39-4762-BFB9-59210DF1719B@microsoft.com...
> >I retyped the code, and I did notice the single apostrophe. Copy/paste
> > didn't make a difference. If I remove the 4 double quotes, I get compile
> > errors. If I remove the 2 double quotes from either side of the
> > ampersands,
> > then it prompts me for the workbook to use each time. If I leave all
> > quotes
> > in, it works, although it is slow.
> >
> > "Rick Rothstein (MVP - VB)" wrote:
> >
> >> Are you saying the code I posted didn't work? Did you copy/paste it or
> >> re-type it? If you re-typed it, did you notice the apostrophe next to the
> >> quote mark that is after the last ampersand?
> >>
> >> Rick
> >>
> >>
> >> "Fred" <Fred@discussions.microsoft.com> wrote in message
> >> news:4340C0D5-1036-4F0F-B003-EFA0AC9161E8@microsoft.com...
> >> > Thanks Rick. Works great with quote marks, doesn't work without them.
> >> >
> >> > Fred
> >> >
> >> >
> >> > "Rick Rothstein (MVP - VB)" wrote:
> >> >
> >> >> Try this...
> >> >>
> >> >> ActiveCell.Formula = "='F:\[Contacts 2008.xls]" & nws & "'!$A1"
> >> >>
> >> >> Just a note... anything between two quote marks is pure text; if you
> >> >> want
> >> >> it
> >> >> to be a variable, it must be concatenated onto the other text parts so
> >> >> that
> >> >> it is not inside a pair of quote marks.
> >> >>
> >> >> Rick
> >> >>
> >> >>
> >> >>
> >> >> "Fred" <Fred@discussions.microsoft.com> wrote in message
> >> >> news:125D12C2-5E43-4D93-B96B-3CBCCE31A7BF@microsoft.com...
> >> >> > Hi
> >> >> > I have a "Contacts" workbook with 12 worksheets in it, one for each
> >> >> > month.
> >> >> > I also have a "Vendor" worksheet, also with 12 worksheets in it. I
> >> >> > need
> >> >> > to
> >> >> > change the formula in cell AA1 of the Vendor worksheet Jan 08 to
> >> >> > read
> >> >> > the
> >> >> > info in cell A1 on the Jan 08 Contacts worksheet. For the next
> >> >> > month,
> >> >> > the
> >> >> > formula needs to move to the Feb 08 sheets, and so on.
> >> >> >
> >> >> > How can I use the sheetname variable "nws" in a formula to reference
> >> >> > the
> >> >> > sheet and enter in the sheetname at that spot?
> >> >> >
> >> >> > Here is a bit of the code that I have for this part:
> >> >> >
> >> >> > Dim nws as string
> >> >> > For Each x In Worksheets
> >> >> > x.Activate
> >> >> > nws = ActiveSheet.Name
> >> >> > Range("AA1:AA1").Select
> >> >> > ActiveCell.Formula = "='F:\[Contacts 2008.xls]nws'!$A1"
> >> >> > Next x
> >> >> >
> >> >> > Thank you
> >> >> > Fred
> >> >>
> >> >>
> >>
> >>
>
>