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" wrote in
message ...
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" wrote in
message ...
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("Detail s").Range("A2:BZ1000"),60,
FALSE)
Range("F16").Copy Range(target)
End If

--
HTH

Bob Phillips

"Defoes Right Boot" wrote in
message ...
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" wrote in
message ...
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("Detail s").Range("A2:BZ1000"),60,
FALSE)
Range("F16").Copy Range(target)
End If

--
HTH

Bob Phillips

"Defoes Right Boot" wrote in
message ...
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" wrote in
message ...
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" wrote in
message ...
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("Detail s").Range("A2:BZ1000"),60,
FALSE)
Range("F16").Copy Range(target)
End If

--
HTH

Bob Phillips

"Defoes Right Boot" wrote in
message ...
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"
wrote in
message ...
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" wrote in
message ...
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("Detail s").Range("A2:BZ1000"),60,
FALSE)
Range("F16").Copy Range(target)
End If

--
HTH

Bob Phillips

"Defoes Right Boot" wrote in
message ...
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"
wrote in
message ...
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