Solved

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

Posted on 2013-12-03
8
243 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 49

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

910 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

20 Experts available now in Live!

Get 1:1 Help Now