Copy text to specific cell.

I need to run a macro from a button which will copy the contents of cell F16
ONLY if it is text rather than a formula. (Basically the default value of the
cell is a formula but the user can overwrite the formula if required - if
they do this I then need to copy their input to another cell.) The
destination cell is also dependent upon the value in cell F4.

I can't seem to get anywhere at all with this - if anyone can help would be
very much appreciated!

Thanks

Phil

If Not Range("F16").HasFormula Then
Select Case Range("F4").Value
Case 1: Range("F16").Copy Range("G16")
Case 2: Range("F16").Copy Range("H16")
End Selec t
End If

--
HTH

Bob Phillips

"Defoes Right Boot" <[email protected]> wrote in
message news:[email protected]...
> I need to run a macro from a button which will copy the contents of cell
F16
> ONLY if it is text rather than a formula. (Basically the default value of
the
> cell is a formula but the user can overwrite the formula if required - if
> they do this I then need to copy their input to another cell.) The
> destination cell is also dependent upon the value in cell F4.
>
> I can't seem to get anywhere at all with this - if anyone can help would
be
> very much appreciated!
>
> Thanks
>
> Phil

Thanks Bob that does the trick.

There are a lot of possibilities for the value in F4 though, is it possible
to select the destination cell in a similar way to a VLOOKUP function rather
than putting in all the different possibilities as CASE options?

E.g. =VLOOKUP($F$4,Details!$A$2:$BZ$1000,60,FALSE) would find the value
currently in the cell I want to use as the destination, can I use something
similar to set the destination cell?

Thanks

"Bob Phillips" wrote:

>
> If Not Range("F16").HasFormula Then
> Select Case Range("F4").Value
> Case 1: Range("F16").Copy Range("G16")
> Case 2: Range("F16").Copy Range("H16")
> End Selec t
> End If
>
> --
> HTH
>
> Bob Phillips
>
> "Defoes Right Boot" <[email protected]> wrote in
> message news:[email protected]...
> > I need to run a macro from a button which will copy the contents of cell
> F16
> > ONLY if it is text rather than a formula. (Basically the default value of
> the
> > cell is a formula but the user can overwrite the formula if required - if
> > they do this I then need to copy their input to another cell.) The
> > destination cell is also dependent upon the value in cell F4.
> >
> > I can't seem to get anywhere at all with this - if anyone can help would
> be
> > very much appreciated!
> >
> > Thanks
> >
> > Phil
>
>
>

If that is a cell address, this should work (untested)

If Not Range("F16").HasFormula Then
target =
Application.VLOOKUP(Range("F4"),Worksheets("Details").Range("A2:BZ1000"),60,
FALSE)
Range("F16").Copy Range(target)
End If

--
HTH

Bob Phillips

"Defoes Right Boot" <[email protected]> wrote in
message news:[email protected]...
> Thanks Bob that does the trick.
>
> There are a lot of possibilities for the value in F4 though, is it
possible
> to select the destination cell in a similar way to a VLOOKUP function
rather
> than putting in all the different possibilities as CASE options?
>
> E.g. =VLOOKUP($F$4,Details!$A$2:$BZ$1000,60,FALSE) would find the value
> currently in the cell I want to use as the destination, can I use
something
> similar to set the destination cell?
>
> Thanks
>
> "Bob Phillips" wrote:
>
> >
> > If Not Range("F16").HasFormula Then
> > Select Case Range("F4").Value
> > Case 1: Range("F16").Copy Range("G16")
> > Case 2: Range("F16").Copy Range("H16")
> > End Selec t
> > End If
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Defoes Right Boot" <[email protected]> wrote in
> > message news:[email protected]...
> > > I need to run a macro from a button which will copy the contents of
cell
> > F16
> > > ONLY if it is text rather than a formula. (Basically the default value
of
> > the
> > > cell is a formula but the user can overwrite the formula if required -
if
> > > they do this I then need to copy their input to another cell.) The
> > > destination cell is also dependent upon the value in cell F4.
> > >
> > > I can't seem to get anywhere at all with this - if anyone can help
would
> > be
> > > very much appreciated!
> > >
> > > Thanks
> > >
> > > Phil
> >
> >
> >

No, sorry Bob I wasn't specific enough.

Basically the sheet "Details" has many rows of data. Column A in that sheet
is a unique reference number, and the value in cell F4 will match one of the
values in that column. I need to find the sixtieth cell in the row on sheet
"Details" which starts with the value in F4 and paste the text from F16 in
there.

Does that make more sense now? Sorry if not, I'm self-taught so not sure if
I'm using the right terms or not!!!

Thanks for your help thus far anyway!

Phil

"Bob Phillips" wrote:

> If that is a cell address, this should work (untested)
>
> If Not Range("F16").HasFormula Then
> target =
> Application.VLOOKUP(Range("F4"),Worksheets("Details").Range("A2:BZ1000"),60,
> FALSE)
> Range("F16").Copy Range(target)
> End If
>
> --
> HTH
>
> Bob Phillips
>
> "Defoes Right Boot" <[email protected]> wrote in
> message news:[email protected]...
> > Thanks Bob that does the trick.
> >
> > There are a lot of possibilities for the value in F4 though, is it
> possible
> > to select the destination cell in a similar way to a VLOOKUP function
> rather
> > than putting in all the different possibilities as CASE options?
> >
> > E.g. =VLOOKUP($F$4,Details!$A$2:$BZ$1000,60,FALSE) would find the value
> > currently in the cell I want to use as the destination, can I use
> something
> > similar to set the destination cell?
> >
> > Thanks
> >
> > "Bob Phillips" wrote:
> >
> > >
> > > If Not Range("F16").HasFormula Then
> > > Select Case Range("F4").Value
> > > Case 1: Range("F16").Copy Range("G16")
> > > Case 2: Range("F16").Copy Range("H16")
> > > End Selec t
> > > End If
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > "Defoes Right Boot" <[email protected]> wrote in
> > > message news:[email protected]...
> > > > I need to run a macro from a button which will copy the contents of
> cell
> > > F16
> > > > ONLY if it is text rather than a formula. (Basically the default value
> of
> > > the
> > > > cell is a formula but the user can overwrite the formula if required -
> if
> > > > they do this I then need to copy their input to another cell.) The
> > > > destination cell is also dependent upon the value in cell F4.
> > > >
> > > > I can't seem to get anywhere at all with this - if anyone can help
> would
> > > be
> > > > very much appreciated!
> > > >
> > > > Thanks
> > > >
> > > > Phil
> > >
> > >
> > >
>
>
>

Aaah! Why didn't you say :-)

Version2/IX/a/13

Dim targetRow As Long

If Not Range("F16").HasFormula Then
On Error Resume Next
targetRow = Application.Match("a", Range("H1:H10"), 0)
On Error GoTo 0
If targetRow <> 0 Then
Range("F16").Copy Range("BH" & targetRow)
End If
End If


--
HTH

Bob Phillips

"Defoes Right Boot" <[email protected]> wrote in
message news:[email protected]...
> No, sorry Bob I wasn't specific enough.
>
> Basically the sheet "Details" has many rows of data. Column A in that
sheet
> is a unique reference number, and the value in cell F4 will match one of
the
> values in that column. I need to find the sixtieth cell in the row on
sheet
> "Details" which starts with the value in F4 and paste the text from F16 in
> there.
>
> Does that make more sense now? Sorry if not, I'm self-taught so not sure
if
> I'm using the right terms or not!!!
>
> Thanks for your help thus far anyway!
>
> Phil
>
> "Bob Phillips" wrote:
>
> > If that is a cell address, this should work (untested)
> >
> > If Not Range("F16").HasFormula Then
> > target =
> >
Application.VLOOKUP(Range("F4"),Worksheets("Details").Range("A2:BZ1000"),60,
> > FALSE)
> > Range("F16").Copy Range(target)
> > End If
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Defoes Right Boot" <[email protected]> wrote in
> > message news:[email protected]...
> > > Thanks Bob that does the trick.
> > >
> > > There are a lot of possibilities for the value in F4 though, is it
> > possible
> > > to select the destination cell in a similar way to a VLOOKUP function
> > rather
> > > than putting in all the different possibilities as CASE options?
> > >
> > > E.g. =VLOOKUP($F$4,Details!$A$2:$BZ$1000,60,FALSE) would find the
value
> > > currently in the cell I want to use as the destination, can I use
> > something
> > > similar to set the destination cell?
> > >
> > > Thanks
> > >
> > > "Bob Phillips" wrote:
> > >
> > > >
> > > > If Not Range("F16").HasFormula Then
> > > > Select Case Range("F4").Value
> > > > Case 1: Range("F16").Copy Range("G16")
> > > > Case 2: Range("F16").Copy Range("H16")
> > > > End Selec t
> > > > End If
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > "Defoes Right Boot" <[email protected]>
wrote in
> > > > message news:[email protected]...
> > > > > I need to run a macro from a button which will copy the contents
of
> > cell
> > > > F16
> > > > > ONLY if it is text rather than a formula. (Basically the default
value
> > of
> > > > the
> > > > > cell is a formula but the user can overwrite the formula if
required -
> > if
> > > > > they do this I then need to copy their input to another cell.) The
> > > > > destination cell is also dependent upon the value in cell F4.
> > > > >
> > > > > I can't seem to get anywhere at all with this - if anyone can help
> > would
> > > > be
> > > > > very much appreciated!
> > > > >
> > > > > Thanks
> > > > >
> > > > > Phil
> > > >
> > > >
> > > >
> >
> >
> >

Great stuff! Many thanks Bob

Phil

"Bob Phillips" wrote:

> Aaah! Why didn't you say :-)
>
> Version2/IX/a/13
>
> Dim targetRow As Long
>
> If Not Range("F16").HasFormula Then
> On Error Resume Next
> targetRow = Application.Match("a", Range("H1:H10"), 0)
> On Error GoTo 0
> If targetRow <> 0 Then
> Range("F16").Copy Range("BH" & targetRow)
> End If
> End If
>
>
> --
> HTH
>
> Bob Phillips
>
> "Defoes Right Boot" <[email protected]> wrote in
> message news:[email protected]...
> > No, sorry Bob I wasn't specific enough.
> >
> > Basically the sheet "Details" has many rows of data. Column A in that
> sheet
> > is a unique reference number, and the value in cell F4 will match one of
> the
> > values in that column. I need to find the sixtieth cell in the row on
> sheet
> > "Details" which starts with the value in F4 and paste the text from F16 in
> > there.
> >
> > Does that make more sense now? Sorry if not, I'm self-taught so not sure
> if
> > I'm using the right terms or not!!!
> >
> > Thanks for your help thus far anyway!
> >
> > Phil
> >
> > "Bob Phillips" wrote:
> >
> > > If that is a cell address, this should work (untested)
> > >
> > > If Not Range("F16").HasFormula Then
> > > target =
> > >
> Application.VLOOKUP(Range("F4"),Worksheets("Details").Range("A2:BZ1000"),60,
> > > FALSE)
> > > Range("F16").Copy Range(target)
> > > End If
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > "Defoes Right Boot" <[email protected]> wrote in
> > > message news:[email protected]...
> > > > Thanks Bob that does the trick.
> > > >
> > > > There are a lot of possibilities for the value in F4 though, is it
> > > possible
> > > > to select the destination cell in a similar way to a VLOOKUP function
> > > rather
> > > > than putting in all the different possibilities as CASE options?
> > > >
> > > > E.g. =VLOOKUP($F$4,Details!$A$2:$BZ$1000,60,FALSE) would find the
> value
> > > > currently in the cell I want to use as the destination, can I use
> > > something
> > > > similar to set the destination cell?
> > > >
> > > > Thanks
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > >
> > > > > If Not Range("F16").HasFormula Then
> > > > > Select Case Range("F4").Value
> > > > > Case 1: Range("F16").Copy Range("G16")
> > > > > Case 2: Range("F16").Copy Range("H16")
> > > > > End Selec t
> > > > > End If
> > > > >
> > > > > --
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > "Defoes Right Boot" <[email protected]>
> wrote in
> > > > > message news:[email protected]...
> > > > > > I need to run a macro from a button which will copy the contents
> of
> > > cell
> > > > > F16
> > > > > > ONLY if it is text rather than a formula. (Basically the default
> value
> > > of
> > > > > the
> > > > > > cell is a formula but the user can overwrite the formula if
> required -
> > > if
> > > > > > they do this I then need to copy their input to another cell.) The
> > > > > > destination cell is also dependent upon the value in cell F4.
> > > > > >
> > > > > > I can't seem to get anywhere at all with this - if anyone can help
> > > would
> > > > > be
> > > > > > very much appreciated!
> > > > > >
> > > > > > Thanks
> > > > > >
> > > > > > Phil
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>