CODEWITHSUNDEEP
excelvbafunctioncount
Chris Dumas
Chris Dumas

Reputation: 37

Count Function Value from One Sheet to Another Sheet in the Same Workbook

This seems like it would be relatively simple. I'm trying to take the value/result of the count function for a column in a sheet ("MASTER" in the code below) and have that value display in a range in a separate sheet in the same workbook ("Placement").

The code below works if I have the value display in the same sheet - MASTER to MASTER for instance - but when I try the code below, 0 displays in A1 on "Placement" instead of 30 which is the actual value.

Sub countdataincolums()

Dim master, placement
Set master = ActiveWorkbook.Sheets("MASTER")
Set placement = ActiveWorkbook.Sheets("Placement")

placement.Range("A1").Value = WorksheetFunction.Count(master.Range("E2:E40"))

End Sub

Any advice is greatly appreciated!

Upvotes: 1

Views: 887

Answers (2)

FaneDuru
FaneDuru

Reputation: 42236

Your problem is the next: The code itself works! Not the most beautiful code, but it should work.

  1. Try changing the variable declaration in this way:

    Dim master As Worksheet, placement As Worksheet

    This will not solve your problem but it can save you from other troubles...

  2. Count function counts numbers and CountA counts strings. Even if you can see a number in a cell, it is a string for VBA if its format is Text. So, if you enter any new numbers in that specific column, formatted as General, Excel will have a good guess and Count function will work well, except the case when the specific column has been formatted like Text, using Text to Columns. If you want to count everything in that specific range, you can format column E:E as Text, but using Text to Column (from Data Tab) and use CountA. It will count numbers and strings, too.

  3. In order to switch back to Count function use, you have to use again Text to Column (after the range selection), proceed exactly the same, but after the second Next, choose General...

  4. Only now I observed your remark a about the missing zero for the first digit. If you would like to see 23 like 23.0, after changing the format in General, using Text to Columns, format the range like Custom and choose ###.0.

Upvotes: 1

Chris Dumas
Chris Dumas

Reputation: 37

@FaneDuru had it figured out...the issue was based on the column formatting:

Try only changing Count with CountA. If it will return as you wish, it is clearly a matter of format, even if you can see numbers

Changing to CountA achieved the desired result and will likely be a durable solution to what I'll need for this particular macro. Reformatting the data via: Data - Text to Columns - Next - Next , choose General and press 'Finish' (per @FaneDuru) and then using count also worked, but removed zeros as the first digit, which I need for this particular dataset.

Basically, the count function was getting confused. Reformatting did unconfuse count, but counta provided the desirable result.

If 'counta' no longer produces the desired outcome...I'll be back asking for assistance on how to convert the data so count can work and then convert it back.

Thanks again for everyone's assistance!

Upvotes: 1

Related Questions