Hot Fox: Re: [Access VBA Central] How to Concatenate multiple columns of numbers?

domingo, 12 de dezembro de 2010

Re: [Access VBA Central] How to Concatenate multiple columns of numbers?

Okay first of all if a column contains a 0 is that considered to be blank as well or just the Null instance (I'll right with a zero being considered nothing)?   Also by saying do not concantenate do you mean skip that col and concantenate the next or skip the col altogether (I'm assuming the latter) -- either/or here is the code you need.   Granted you might have to tailor the syntax a bit since I know numerous languages and sometimes do not recall the exact syntax for VBA in conjunction with Excel.

...I assume you have done what you need to connect to the source worksheet and the destination worksheet you are using.

** There is a way to get the Row Count so you make this dynamic if need be
max_num_rows = 2137
max_num_cols = 100

dest_row = 0
For row_idx = 0 to max_num_rows
     itration_cnt = 0
     col_cnt = 0
     dest_col = 0
     For col_idx = 0 to max_num_cols
           itration_cnt = itration_cnt + 1
           ** Cannot recall if this the following is a function or not
           If wrksheet(row_idx, col_idx) Is Not Null Then
                If wrksheet(row_idx, col_idx) <> 0 Then ** remove this IF - if this is not the case
                    If itration_cnt = 1 Then
                         NumStr = NumToString(wrksheet(row_idx, col_idx))
                         col_cnt = 1
                    Else
                         itration_cnt = 0
                         If col_cnt = 1 Then
                              NumStr = NumStr & "_"
                         End If
                         NumStr = NumStr & NumToString(wrksheet(row_idx, col_idx))
                         DestWrkSheet(dest_row, dest_col) = NumStr
                         dest_col = dest_col + 1
                         col_cnt = 0
                    End If
                End If
           End If
     Next col_idx

    dest_row = dest_row + 1
Next row_idx

Okay the NumToString function is an instrinsic function but I think it has more parameters and may be named differently. I put this one in to denote what/where you are using.

I hope that is what you were looking for.

--- On Sat, 12/11/10, jeffdeck2001 <jeffdeck2001@yahoo.com> wrote:

From: jeffdeck2001 <jeffdeck2001@yahoo.com>
Subject: [Access VBA Central] How to Concatenate multiple columns of numbers?
To: AccessVBACentral@yahoogroups.com
Date: Saturday, December 11, 2010, 6:59 AM

How to Concatenate multiple columns of numbers?
I am using Excel 2007 VBA -- and need to concatenate 100 columns
of numbers into 50, with 2137 rows. If a cell is empty, then do
not concatenate. End script when first empty row is reached.
Two columns of numbers will be concatenated and separated by
an underscore.

Numbers in each cell come from database, and will appear
to be random to the casual observer, though each number has
significant meaning.

Example:

Columns
A1----B1----C1----D1----E1----F1
12----2----13----1----256----3

After VBA script is run:

Columns
A1------B1------C1
12_2----13_1----256_3

[Non-text portions of this message have been removed]