Copy text to specific cell.

[Guest]

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

 

[Bob Phillips]

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

 

[Guest]

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]

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

 

[Guest]

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]

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

 

[Guest]

Great stuff! Many thanks Bob

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