Problem with pasting zeros

DeeEmmEss

New Member
Joined
Nov 25, 2015
Messages
41
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,644
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,148,324
Messages
5,746,121
Members
423,993
Latest member
Vighper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top