Solved

copy data to another column in same row if nulls exist FROM another column of information

Posted on 2013-12-03
8
242 Views
Last Modified: 2013-12-03
excel 2010 vba

Userform listbox:

I'am populating a listbox via an array.


Arr = objMyRecordset.GetRows
       objMyRecordset.MoveFirst

With UserForm2.ListBox1
    .Clear
    .ColumnCount = objMyRecordset.Fields.Count
    .Column = Arr
    .ColumnWidths = "4 pt;43 pt;50 pt;180 pt;34 pt;250 pt;34 pt;100 pt; 30 pt;  30 pt;


before the data is diplayed in the listbox:

I was wondering if i could do the following:



BEFORE
MATCHTYPE   Gsku        Gname         Gnumber      Gdescription             Gtype    vendorname         vendornumber     compname       compnumber
BXREF       2A121      lysol          112          Spray disinfectant 110z    F       LYSOL               1121            MSC             32121
BXREF       1A001      UVEX           432          Glasses clear              F                                           BARNES          5431   <------
BXREF       3A342      rubbermaid     7686         Trash Can Liners           F       rubbermaid          76861           MSC             32121
BXREF       4Z232      lysol          112          Spray disinfectant 4oz     F       LYSOL               1121            MSC             32121
BXREF       7W212      WD40           WD40         Spray LUBRICANT 8oz        F                                           FASTENAL        65431  <-----



AFTER
MATCHTYPE   Gsku        Gname         Gnumber      Gdescription             Gtype    vendorname         vendornumber     compname       compnumber
BXREF       2A121      lysol          112          Spray disinfectant 110z    F       LYSOL               1121            MSC             32121
BXREF       1A001      UVEX           432          Glasses clear              F       BARNES              5431            BARNES          5431
BXREF       3A342      rubbermaid     7686         Trash Can Liners           F       rubbermaid          76861           MSC             32121  <----
BXREF       4Z232      lysol          112          Spray disinfectant 4oz     F       LYSOL               1121            MSC             32121
BXREF       7W212      WD40           WD40         Spray LUBRICANT 8oz        F       FASTENAL            65431           FASTENAL        65431  <----

Open in new window



Basically:
if vendorname     AND    vendornumber  are blank for any given row of data.

i would like to know if i can fill those blanks with data from
compname       compnumber   AS long as these 2 columns have data in them.

BUT ONLY IF THE MATCH TYPE COLUMN SAYS   "BXREF" FOR THAT ROW.


Thanks
fordraiders
0
Comment
Question by:fordraiders
  • 5
  • 2
8 Comments
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39691894
HI,

Maybe something like this

For Idx1 = LBound(arr, 1) To UBound(arr, 1)
    If arr(Idx1, 1) = "BXREF" And arr(Idx1, 8) = "" And arr(Idx1, 9) = "" _
            And arr(Idx1, 10) <> "" And arr(Idx1, 11) <> "" Then
        arr(Idx1, 8) = arr(Idx1, 10)
        arr(Idx1, 9) = arr(Idx1, 11)
    End If
Next

Open in new window

Regards
0
 
LVL 4

Expert Comment

by:andrew_man
ID: 39691895
Would you mind to post your spreadsheet ?
0
 
LVL 4

Expert Comment

by:andrew_man
ID: 39692335
If you give us your spreadsheet, we can amend of your code on behalf of you.
0
 
LVL 3

Author Comment

by:fordraiders
ID: 39692587
andrew, The data is never placed on a sheet...it goes into a listbox on a userform.
the data is coming from an external source. of which i cant post where it is coming....


So i have manipulate the array in memory...before placing the data on the userform listbox.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 3

Author Comment

by:fordraiders
ID: 39692599
andrew, or even manipulate the listbox right after it presented in the listbox..
as per the code below.

Example:
' this little snippett...gets rid of anytthing in the listbox that in Column 8 is equal to WG, WV,DG, DV

With UserForm2.ListBox1
    For ri = .ListCount - 1 To 0 Step -1
        If .List(ri, 8) = "DG" Or .List(ri, 8) = "WD" Or .List(ri, 8) = "DV" Or .List(ri, 8) = "WV" Or .List(ri, 8) = "WG" Then
            .RemoveItem ri
        End If
    Next
End With
0
 
LVL 3

Author Comment

by:fordraiders
ID: 39693979
rgonzo..et all
is there a way to see the value of this in debug mode...for that column and row...while in debug.

or can the same thing be done in the listbox once it gets loaded ?
0
 
LVL 3

Author Comment

by:fordraiders
ID: 39693997
With UserForm2.ListBox1
    For ri = .ListCount - 1 To 0 Step -1
        If .List(ri, 1) = "BXREF" And .List(ri, 18) = "" And .List(ri, 19) = "" And .List(ri, 13) <> "" And .List(ri, 15) <> "" Then
            .List(ri, 18) = .List(ri, 15)
            .List(ri, 19) = .List(ri, 13)
        End If
    Next
End With


got it...!
0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 39693999
beautiful...got me on the right path.
Thanks
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now