Looking for VBA code to Paste Special Value only if cell in Range is Populated

[jbench18]

Hey everyone,

First time post on here. I figured I can reach out to some of you guru's as I've been trying to figure this out on my own, and haven't been able to. I'm trying to find a way to paste special value cell(s) in a range ONLY if the cell contains a number. The attachment should help explain, what I'm trying to do, but the end result should have all the cells C2:F32 populated with a number (2nd tab titled "Scoring"). Below is the current code I'm working with...

Sub MacroLogData()

Sheets("Scoring (2)").Range("C2:F32").Copy Sheets("Scoring").Range("C2")

With Sheets("Scoring").Range("C2:F32").SpecialCells(xlFormulas, 1)
.Value = .Value
End With

Sheets("Scoring").Range("G2") = ""
Sheets("Response Tool").Range("C5").ClearContents

End Sub

 

[sky1in5]

i dont get what you trying to explain, are you going to paste the special value from C2 to F32 into other sheet C2 to F32 if the value is a number?

 

[sheetspread]

Did you try using if isnumeric then .value = .value?

 

[jbench18]

I want to copy / paste the formulas only if there are NO values currently loaded. Basically I am looking for a code that says only copy and paste the formulas if the cell(s) in the range of C2:F32 are blank. The problem is when I answer one question it does the paste special value. Then when I run the macro again the formula is replacing the paste special value.

For example..if the response to the first question gets populated in cell C5, the next time I run the macro I want it to populate the range (C2:F32) with the formula except for C5. Let's say the response to the 2nd question gets populated in D5. For the third question when the macro is run I want it to populate the range with formula except for C5 and D5. Ultimately in the end all the cells in the range will have a numeric value bw 1-5. I hope this helps clarify.

 

[jbench18]

Is it possible to attach the spreadsheet I'm working with..it's probably easier to look it.

 

[sheetspread]

There's nowhere to post attachments on this forum (unless you have Level 6 MrExcel clearance or something) but this is good for posting screenshots that can be copied:

http://cid-8cffdec0ce27e813.office.live.com/browse.aspx/MrExcel