VBA Array Code

Raghava@rock · Apr 25, 2016

Hi Team,

I need your help to provide a code which will check if there is an item # entered and nothing about its descriptions will pop up a Msg Box saying that specific Item # description is not entered , if there are Msg Box Saying There are multiple Items # with Description.

The Array range should be from C5 :C25 .

Attached is the sample File

Raghava@rock · Apr 25, 2016

Just to keep it more simple the code to run through for those cells which are not blank in range C5 :C25 and then check if the respective cell in column D is not bank for the cells in whic are filled with data. If found then pop up a MSG box

chirayu · Apr 25, 2016

Code:

Sub Looper()

Dim MyItems As String
Dim LstRow As Integer

'Count last non blank row in column C
LstRow = Cells(Rows.Count, "C").End(xlUp).Row

'Start Cell
Range("C5").Select

'Loop for Item with no Description
For i = 5 To LstRow 'Change the 5 to whatever is your start row
    If ActiveCell.Offset(0, 1) = "" Then
        If Len(MyItems) = 0 Then
            MyItems = ActiveCell
        Else
            MyItems = MyItems & ", " & ActiveCell
        End If
            ActiveCell.Offset(1, 0).Select
    Else
        ActiveCell.Offset(1, 0).Select
    End If
Next

'Messagebox at end
If Len(MyItems) = 0 Then
    MsgBox "All items have descriptions", vbInformation, ""
Else
    MsgBox "Below item# need descriptions:" & vbNewLine & vbewline & MyItems, vbCritical, ""
End If

End Sub

Ashhu · Apr 25, 2016

Dear Chirayu,
you forgot to save file in macro enabled sheet,
i have updated your attachment and uploaded here.

chirayu · Apr 25, 2016

Ashhu said:
Dear Chirayu,
you forgot to save file in macro enabled sheet,
i have updated your attachment and uploaded here.

I never add my code to .xslm files unless expressly stated otherwise by thread creator. I just work on the sample file provided & upload the code

Ashhu · Apr 25, 2016

chirayu said:
I never add my code to .xslm files unless expressly stated otherwise by thread creator. I just work on the sample file provided & upload the code

Then I misunderstood, sorry

Small edit required in your code, "i" need to be declared, it popped error msg for me. May be because of Open Explicit line at top.
There is typo in last msg box, vbnewline, ( Second) reading wrong.

-Ashwin

Raghava@rock · Apr 25, 2016

Hi Team,

Code is really helpfull, but i was thinking that i would get something on Array , if there could be something as such would be great .

Also i have one question on the VBA Before close event of the work book , can this code be called or triggered from a Module instead of THISWORK.

Marc L · Apr 25, 2016

Hi !

Message box demonstration using Excel basics
and working whatever the module :

Code:

Sub Demo()
'                Dim V    ' Variant array only with Option Explicit, useless without
    With Sheet1
        .Activate
        V = Filter([TRANSPOSE(IF(D5:D25="",IF(C5:C25>0,ADDRESS(ROW(D5:D25),4,4))))], False, False)
        If UBound(V) > -1 Then
            .Range(V(0)).Select
            MsgBox "Missing in " & Join(V, " & "), vbExclamation, "  Item Description"
        End If
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !

Raghava@rock · May 2, 2016

Marc L said:
Hi !

Message box demonstration using Excel basics
and working whatever the module :

Code:

Sub Demo() ' Dim V ' Variant array only with Option Explicit, useless without With Sheet1 .Activate V = Filter([TRANSPOSE(IF(D5:D25="",IF(C5:C25>0,ADDRESS(ROW(D5:D25),4,4))))], False, False) If UBound(V) > -1 Then .Range(V(0)).Select MsgBox "Missing in " & Join(V, " & "), vbExclamation, " Item Description" End If End With End Sub

Do you like it ? So thanks to click on bottom right Like !

Hi ,

Could you please provide an understanding of filter option and the 4,4 referring to that is used in Code.

Regards
Raghava.

YasserKhalil · May 2, 2016

Hi Raghava@rock
The Address function has five parameters : the first, second are not optional where the third / fourth / fifth are optional

1- The first parameter is row number and as provided it would produce the row numbers for the range D5:D25
2- The second parameter is column number which is here 4 that refers to column D
3- the third which is optional here is 4 which gives relative address like that D5 / D6 and so on
1 = Absolute
2=absolute row / relative column
3= relative row / absolute column
4=relative

Hope this helps you

Raghava@rock · May 2, 2016

Hi
I made some adjustments to the provided code as my requirement .

E = Filter([TRANSPOSE(IF(B64:B183="",IF(F64:F183="YYY",IF(G64:G183>0,ADDRESS(ROW(G64:G183),2,4)))))], False, False)

While i am trying to add another condition IF(F64:F183="XXX" it is throwing an error.

Please help.

Marc L · May 2, 2016

You have 2 values to test on exactly the same range
so it could be difficult to follow this formula way.
As we don't have any sample workbook in relation with this need …

Within the IF for the range, try an OR operator between both values …

YasserKhalil · May 3, 2016

According to Mr. Marc L post
this may be like that

Code:

E = Filter([TRANSPOSE(IF(B64:B183="",IF(OR(F64:F183="YYY",F64:F183="XXX"),IF(G64:G183>0,ADDRESS(ROW(B64:B183),2,4)))))], False, False)

Raghava@rock · May 3, 2016

Hello ,

This time i need the same with AND criteria attached is the file . below is the code I changed , like always its not working ....

V = Filter([TRANSPOSE(IF(D5:D25="",IF(AND(B5:B25="All",C5:C25>0),ADDRESS(ROW(D5:D25),4,4))))], False, False)

Marc L · May 3, 2016

Do not use AND operator but IF within an IF in formula.
As I didn't succeed with AND within an array via Evaluate method,
I suppose it's the same for OR operator …

By the way, it would be easier for you to go to classic VBA way
like Chirayu's post. And instead of an array you could use a string variable
to work with the message box …

jindon · May 3, 2016

Code:

MsgBox Join(Filter([transpose(if((b5:b1000="All")*(c5:c1000>0)*(d5:d1000=""),address(row(b5:b1000),4,4),char(2)))], Chr(2), 0), vbLf)

Quite a few worksheet functions do not work with evaluate method with array.
And Or...etc, I would really like to see if they work...

If you want to learn a bit of Evaluate Method, the link is the thread I first introduced as a solution for someone.
http://www.mrexcel.com/forum/excel-questions/246143-macr-change-positive-values-negative.html
pgc01 is giving good explanation.

Few more detailed example.
http://www.ozgrid.com/forum/showthread.php?t=183815

Marc L · May 3, 2016

Yes often I use IF, ROW or TRANSPOSE to suit like FormulaArray …

Raghava@rock · May 5, 2016

Hi Marc L,

I have used the provided code (arrayone) , but this time the change is that my column D is with merged cell but filled with required information but code is still picking the all cells except the first cell of the merged one which has value .

Could you please help me provide the code to pick only the address of first merged cell with out any text in it while Coulmn "C have value.

Just one more point to note that merged cells will be always 3 cells

Thanks a ton in advance

Raghava

Marc L · May 5, 2016

That way can't be used with merged cells !

Again, you must go to classic way like Chirayu's post …

jindon · May 5, 2016

Code:

Sub test()
    With [c4].CurrentRegion
        With .Offset(1).Resize(.Rows.Count - 1).Columns(2)
            MsgBox Join(Filter(Evaluate("transpose(if((mod(row(1:" & .Rows.Count & "),3)=1)*(" & .Address & _
            "=""""),address(row(" & .Address & "),4,4),char(2)))"), Chr(2), 0), vbLf)
        End With
    End With
End Sub

Marc L · May 5, 2016

Great formula jindon !

But he asked in his last attachment for D23,
the reason why I didn't really undersand its need …

jindon · May 6, 2016

I've got D23.

Marc L · May 6, 2016

My bad … 'cause I am under 2003 I had to copy data to a new workbook
and I didn't saw that first displayed row was #2 instead of #1,
the reason why I was confused and couldn't understand its need …

Raghava@rock · May 6, 2016

This works as per requirement , Jindon i kindly request you to provide explain the formula so that i have some learning too

With .Offset(1).Resize(.Rows.Count - 1).Columns(2)
MsgBox Join(Filter(Evaluate("transpose(if((mod(row(1:" & .Rows.Count & "),3)=1)*(" & .Address & _
"=""""),address(row(" & .Address & "),4,4),char(2)))"), Chr(2), 0), vbLf)

Raghava@rock · May 17, 2016

Hey Could some Explain the below formula used ?
With .Offset(1).Resize(.Rows.Count - 1).Columns(2)
MsgBox Join(Filter(Evaluate("transpose(if((mod(row(1:" & .Rows.Count & "),3)=1)*(" & .Address & _
"=""""),address(row(" & .Address & "),4,4),char(2)))"), Chr(2), 0), vbLf)

VBA Array Code

Member

Attachments

Member

Well-Known Member

Attachments

Active Member

Attachments

Well-Known Member

Active Member

Member

Excel Ninja

Member

Well-Known Member

Member

Excel Ninja

Well-Known Member

Member

Attachments

Excel Ninja

Well-Known Member

Excel Ninja

Member

Attachments

Excel Ninja

Well-Known Member

Excel Ninja

Well-Known Member

Attachments

Excel Ninja

Member

Member