Solved: VBA Help

[leeadam71]

I am trying to write some VBA for Excel 2003 that will look threw a column of numbers like 1,2,3,4,5,6. Example A1=1,A2=2,A3=1,A4=4, etc. I need the code to look and see if there is a 1, and if there is move the corresponding information on the row next to it to a different cell. What makes it really tricky is that i want it to combine every place there is a 1 into one cell. It will actually go from 1 to 36, and there will be three of each number in the row.

So To summarize i want to find all the like numbers and take there corresponding data and merge it into one cell. I appreciate all help in Advance, because I am very lost.

[s.schwantes]

leeadam71 -

this link should get you started w/ a formula to identify duplicates:

http://www.exceltip.com/st/Find_dupl...Excel/605.html

then, I'd suggest once you're comfortable with the formula and the addtional steps needed to meet your objective, turn on your macro recorder, and try to generate the code automatically by stepping through the process one step at a a time.

good luck

Steve Schwantes

[leeadam71]

Let me start at my first problem how do I write an if statement that looks through an entire column and can pull out info like below if the statement is TRUE. I would like it to be in a macro form that I could run manually. Not just a formula.

=IF(E2="02",A2, IF(E2="28",A2,"")),

[s.schwantes]

Not sure I understand your objective here ... ???

if you're looking to find values in an entire column or array and to return those to a single cell, why not use a vlookup?

otherwise, if you're looking to identify a matching value cell by cell, you could use your nested IF statement and just copy it down the column to return a number of cells...

Or, you could use the formula found at the link I provided y'day.

If you're trying to automate this process w/ a macro. My suggestion is to first make sure it works manually. Then, delete your work, and start over using the recorder. Then, study the resulting VBA code in the VBE. Run the macro a few times to see if it works. If not, you can then post the code and seek help w/ more specific issues or problems.

When you post your code, be sure to put the VBA tags around it.

[CreganTur]

One way to accomplish this via VBA... at least for evaluating the value of the individual cells, is to use SELECT CASE statement.

[VBA]Dim rng1 As Range

For Each rng1 In Range("***Enter Range Here***")
Select Case rng1.Value
Case 1
'***code to complete
Case 2
'***code to complete
'create a different Case check for all the numbers you want to work
End Select[/VBA]

Where you enter the range, you can hard code a static range, or you can use a named range. If you want to use a named range and make it dynamic, then look at this thread, specifically posts 6 & 7 for 2 different ways to create dynamic named ranged.

[mdmackillop]

A simple example would assist here. Use Manage Attachments in the Go Advanced reply section.

[CreganTur]

Here's an example spreadsheet that accomplishes what I think you're trying to do.

Testing the code:

1. Download the attached file
2. Open the workbook
3. Open the VBE (alt+F11)
4. Navigate to Module1
5. Place your cursor anywhere within the Sub EvalAndCombine
6. Press F5

This will cause the code to kickoff- it will evaluate the values of A2:A10 and concatenate all values from Column B with same A value into a specified cell in Column D.

HTH

[mikerickson]

This UDF may do what you want.
The first three arguments for ConcatIf mimic the arguments for SUMIF. The optional Delimiter and NoDuplicates arguments are self explainitory. See attached for an example.
[VBA]Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
Dim i As Long, j As Long
With compareRange.Parent
Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
End With
If compareRange Is Nothing Then Exit Function
If stringsRange Is Nothing Then Set stringsRange = compareRange
Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
stringsRange.Column - compareRange.Column)

For i = 1 To compareRange.Rows.Count
For j = 1 To compareRange.Columns.Count
If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
End If
End If
Next j
Next i
ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1)
End Function[/VBA]

[leeadam71]

Cregantur thank you very much that is doing pretty much what I wanted.

[CreganTur]

Cregantur thank you very much that is doing pretty much what I wanted.

Glad to help

If you need more help with this, let us know.

If this solves your problem, then please mark your thread as solved (Thread tools -> Mark as Solved -> Submit).