Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-12-03
8
Medium Priority
?
256 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 53

Accepted Solution

by:
Rgonzo1971 earned 2000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

885 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