Problem with pasting zeros

[DeeEmmEss]

I have the following sub which copies and pastes a range of data which has been input by user on one sheet (Input) and appends it on (All Scores), which is a master sheet of a thousand or so players and a weekly score over a forty week season. The probelm is with zeros. I have set the workbook not to display zeros which works fine, but the zeros although not displayed are there and count as if the player played that week but scored zero. Occasionally, the player may play and score zero which then really confuses the issue. How do I copy and paste the data which may include a zero and needs to be pasted as such, but blanks need to be shown as blanks? This sub calls another sub at the end to reset the cell with a value in to a formula, becuase as well as inputting data, the user can scroll through previous scores so each cell has a formula in =IFERROR(OFFSET(INDEX(AllPlayerNames,MATCH($I$3,AllScoresTeams,0),1),,$D$2),"")
which works fine, and has a custom format - 0;;;

I have tried umpteen combinations but am frustrated.

Sub Scoresup()
'
' Update Scores
'

'After inputting fixture scores on Input sheet, copies the whole team score
' for both home team and away, and inserts them on All Scores sheet

Application.ScreenUpdating = False
Dim htm As String, atm As String, wk As String
Dim hnum As Integer, anum As Integer

htm = Sheets("Input").Range("I3") 'I3 = Home Team
hnum = Sheets("Input").Range("I2") + 3 'hnum= number of Home Team Players
anum = Sheets("Input").Range("K2") + 3 'anum=number of Away Team Players
atm = Sheets("Input").Range("K3") 'K3 = Away Team
wk = Sheets("Input").Range("D2") 'D2 = week game played

Sheets("Input").Range("J4:J" & hnum).Select 'select Home Team Player scores
Selection.Copye
Sheets("All Scores").Activate
Range("A1").Select
Cells.Find(What:=htm, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0, wk + 1).Activate 'find and select Home Team, Player, & Week game played

Selection.PasteSpecial Paste:=xlPasteValues
Sheets("Input").Activate
Sheets("Input").Range("L4:L" & anum).Select 'select Away Team Player scores
Selection.Copy
Sheets("All Scores").Activate
Range("A1").Select
Cells.Find(What:=atm, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0, wk + 1).Activate 'find and select Away Team, Player & Week game played
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range("a1").Select
ResetScores
End Sub

 

[steve the fish]

Firstly get rid of the formatting. You need to see what is displaying in the cell. Why are you doing it if you can score zero anyway? If you are using offset it is going to show zero if a cell is empty. Try this instead eg:

=IF(OFFSET(A1,0,1)="","",OFFSET(A1,0,1))

This will show a blank if a cell is blank and a zero if a cell shows 0.