VBA, range() help required

[snowmansmartie]

Hi guys,

I have a really easy problem that someone with half a brain cell will do in about 3 seconds, but it seems that I lost my half of a brain cell and figure this out.

I have a table of 4 columns, I want to iterate through the 4th column and if it finds a cell = to True then return the information in the other 1st 3 columns of that same row:

19/06/2013	ABC Company	123456	True
18/06/2013	123 Biz Ltd	654321

<TBODY>
</TBODY>

Assume the above is top left cell is A2 down to D3.

The code I currently have is:

For Each c In Worksheets("Sheet1").Range(A2:D3).Cells
        
        TempRange = ""
        TempRange = "A" & c.Row & ":" & "C" & c.Row

        If c.Value = "True" Then
            CasesList = CasesList & Range(TempRange).Value & vbNewLine
        End If
    
Next

The above gives a blank result within the CasesList variable. If I change the .Value to .Select I then get the results of the D column where D = True.

This shouldnt be beyond me, but I've been looking at this for hours and just cant figure out what I have got wrong.

Any help would be appreciated.

Cheers

 

[Rick Rothstein]

Hi guys,

I have a really easy problem that someone with half a brain cell will do in about 3 seconds, but it seems that I lost my half of a brain cell and figure this out.

I have a table of 4 columns, I want to iterate through the 4th column and if it finds a cell = to True then return the information in the other 1st 3 columns of that same row:

19/06/2013	ABC Company	123456	True
18/06/2013	123 Biz Ltd	654321

<TBODY>
</TBODY>

Assume the above is top left cell is A2 down to D3.

The code I currently have is:

For Each c In Worksheets("Sheet1").Range(A2:D3).Cells
        
        TempRange = ""
        TempRange = "A" & c.Row & ":" & "C" & c.Row

        If c.Value = "True" Then
            CasesList = CasesList & Range(TempRange).Value & vbNewLine
        End If
    
Next

The above gives a blank result within the CasesList variable. If I change the .Value to .Select I then get the results of the D column where D = True.

This shouldnt be beyond me, but I've been looking at this for hours and just cant figure out what I have got wrong.

It is not easy imagining what you want for output from code you say does not work. Could you post an example where two lines are marked as True and then show us what you want the CaseList variable to contain (show us how it would appear if printed out or MessageBox'ed).

 

[snowmansmartie]

Hi Rick,

Thanks for your reply.

What I am looking for is to create a list that gets put into an Email.

19/06/2013	ABC Company	123456	True
19/06/2013	123 Co Ltd	654321
18/06/2013	Comapny Co Ltd	987654	True

<TBODY>
</TBODY>

Based on the data above the CasesList variable would look something like:

19/06/2013 ABC Company 123456
18/06/2013 Company Co Ltd 987654

The Email part is done and works it is just collecting the data. However I have thought this morning that the formatting may not work properly either, but at this moment just getting it to do what I need is the important part.

Cheers

 

[snowmansmartie]

Hi Rick,

Thanks for your reply.

What I am looking for is to create a list that gets put into an Email.

19/06/2013	ABC Company	123456	True
19/06/2013	123 Co Ltd	654321
18/06/2013	Comapny Co Ltd	987654	True

<TBODY>
</TBODY>

Based on the data above the CasesList variable would look something like:

19/06/2013 ABC Company 123456
18/06/2013 Company Co Ltd 987654

The Email part is done and works it is just collecting the data. However I have thought this morning that the formatting may not work properly either, but at this moment just getting it to do what I need is the important part.

Cheers

 

[Rick Rothstein]

The following lines of code will assign the list you seek to a variable I named "TheList"...

TheList = Join(Application.Transpose(Evaluate("IF(D1:D3=TRUE,A1:A3&"" ""&B1:B3&"" ""&C1:C3,"""")")), vbLf)
Do While InStr(TheList, vbLf & vbLf)
  TheList = Replace(TheList, vbLf & vbLf, vbLf)
Loop

Obviously, you will have to change the red highlighted row numbers to cover your actual data.

 

[snowmansmartie]

Hi Rick,

That worked great thanks. :D

My only issue now is the date column (A) when it picks it up converts it to a date serial rather keeps the formatting. whats the easiest way of preserving the formatiing of these?

Cheers

 

[snowmansmartie]

Can anyone help with this?

Cheers

 

[Firefly2012]

Hi

You can probably modify Rick's code to:

TheList = Join(Application.Transpose(Evaluate("IF(D1:D3=TRUE,TEXT(A1:A3,""dd/mm/yyyy"")&"" ""&B1:B3&"" ""&C1:C3,"""")")), vbLf)
Do While InStr(TheList, vbLf & vbLf)
  TheList = Replace(TheList, vbLf & vbLf, vbLf)
Loop

 

[snowmansmartie]

Thank you so much Firefly and Rich. You guys really helped me sort this out.

Cheers guys