Solved

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

Posted on 2013-12-03
8
247 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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
 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 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