Solved

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

Posted on 2013-12-03
8
252 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
8 Comments
 
LVL 52

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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
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 …
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…

630 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