I need to manipulate a matrix of data into a concatenated string

[DPlank]

Hello.

I've asked this in the Microsoft Office forum, but it's getting awfully VBA based, so I thought I'd transfer it here.

I have a table that looks something like :

A B C D E F
1 New ................ 18/06/02
2 Closed .............. 15/05/03
3 Open ................ 19/05/03
4 New ................. 12/06/03
5 New ................. 08/06/03


I need to write some VBA that will look across this data and return a string of the A column (ID numbers) separated by commas, where the status in B is New and the Date in F is more than 5 days ago. In the above extract, I'd get

 1,5

as the answer.

I've been given code to run a macro to concatenate the strings, here it is :

Sub Concat()

Dim LastRw As Long
Dim Rng As Range
Dim str As String

str = Range("A1").Value

LastRw = Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Range(Cells(1, "A"), Cells(LastRw, "A"))
    
   For x = 1 To Rng.Rows.Count
      str = str & "," & Rng.Rows(x).Value
   Next x
   
Range("B1") = str

End Sub

Anyone know how to do the rest? I'm planning to set up a button to run these macros from a different workbook. I've got the skeleton code set up, but it needs finetuning.

Hope you can help
David

 

[ssVBAdev]

Well, if I understand your question correctly the code above will not accomplish what you want - period. First of all, I (and this is my opinion) don't like the way you are finding the last row - but that's an entirely different topic being handled by another thread (I'd link you to it but I still don't know how on this site yet). But, anyway, I would try somthing like...


Sub Concat()
Dim i As Integer 'Holds the Row number variable
Dim LastRow As Integer 'Find this however suits you best
Dim str As String

str = ""

For i = 1 To LastRow
If UCase(ActiveSheet.Cells(i, 2)) = "NEW" And _
FormatDateTime(ActiveSheet.Cells(i, 6), 0) < FormatDateTime(Date - 5, 0) Then
If Not str = &quot;&quot; Then str = str & &quot;, &quot;
str = str & ActiveSheet.Cells(i, 1)
End If
Next i
MsgBox str
End Sub


The dates can be a bit funny. You may need to change the formatting of your dates.

 

[DPlank]

How would you suggest I go about finding the last row?

I'm no VBA expert(in fact what VBA I've created has been through copying recorded macros and fiddling)

 

[xlbo]

lRow = range(&quot;A65536&quot.end(xlup).row
should do the trick for you. If you want some different ways, have a look at the 2 FAQs on this topic

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[ssVBAdev]

The Macro Recorder is an excellent way to learn how to do things in VBA! Keep using it. Just keep in mind that when you get code from the recorder, try to &quot;play&quot; with it a little and experiment to find better or more efficient ways to do things (the macros recorder can tend to give some code that is correct - but redundant). Great tool though.

Aside from the FAQ's that Geoff mentioned, the thread I was talking about is below (I still don't know how to link you to it yet). There is great debate over this issue. Really what works best is what works best for you (deep huh ;-)).

&quot;find the farthest used cell&quot;

 

[xlbo]

ssVBADev - to link to a thread - just copy the thread number from the thread title:
thread707-575362
for this thread - literally copy and paste

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[ssVBAdev]

xlbo: Thanks for the tip