Solved: Looping

[Odyrus]

Good day all,

I have data in the following range, A3:A14. I need to develop a macro that will loop through this data until a match is found and then pasta some information in adjacent cells. The macro will be matching against data in a cell populated by the user. I'm stuck on how to get the looping until match syntax down but have the rest. Honestly, not really sure where to begin. Greatly appreciate any assistance.

Cheers!

[CatDaddy]

[VBA]For each cell in Range("A3:A14")
If cell.value = myvalue Then
'copy/paste some data
End if
Next cell[/VBA]

[Odyrus]

I do believe I was WAY over thinking this one!

Much appreciated CatDaddy!

Cheers!

[CatDaddy]

no worries friend! remember to mark it solved if you have a solution to your problem, if you need more help then just give some more details

[Odyrus]

I think I could still use a bit of help on this one.

My goal is to have the user select a type of product from a list in sheet1 (range=g2) and have data in sheet1 (various cells, not contiguous)populate a table on sheet2 next to that product name.

I understand I need to set g2 to be myvalue:
[VBA]ActiveWorkbook.Sheet1.Range("g2") = myvalue[/VBA]

But getting the loop and copy syntax is giving me mre trouble than expected.

[CatDaddy]

how far have you gotten on your own? can you post your code?

and you would want
[VBA]myvalue = ActiveWorkbook.Sheet1.Range("g2").value[/VBA]

[CatDaddy]

[VBA]Sub test()
Dim lr As Long, r As Long
Dim myvalue As String
Dim cell As Range
ActiveWorkbook.Sheets(1).Activate
lr = Range("A" & Rows.Count).End(xlUp).Row
r = 1
myvalue = Range("G2").Text
For Each cell In Range("A3:A" & lr)
If cell.Text = myvalue Then
cell.EntireRow.Copy Destination:=Sheets(2).Range("A" & r)
r = r + 1
End If
Next cell
End Sub[/VBA]

[Odyrus]

I'm quite afraid I haven't gotten very far with this segment of code.

The report I'm working on is an amalgam of two separate reports; several sheets have data populated based on odd date intervals and several sheets act more like a monthly role up of the interval sheets. All the original data is placed on a template sheet because it comes from a multitude of various external sources, and then it is placed accordingly from the template sheet to the rest of the report (currently an arduous manual task; the joy of inheritance).

The first bit of code for the interval sheets do the following: take this cell value from X on this template sheet and place it in the next available space, in X row, in X sheet.

[vba]Dim i As Long


'interval data: first variable
With Worksheets("sheet1")
i = .Range("B" & Rows.Count).End(xlUp).Row
.Range("B" & i + 1).Formula = "='template'!D14"
End With[/vba]

The monthly data is laid out a little differently so I need a slightly different solution. My thought was to have the user select the month from a list box. The value in the list box would direct where the monthly information on the roll up sheets would be placed once copied from the template sheet. I'm open to suggestions on a better way to do this but my VB is slightly rusty.

[CatDaddy]

did you try the code i posted?

[Odyrus]

Hello CatDaddy, thanks for replying.

I tried your code but I didn't have any success. I believe I need to specify the ranges in sheet1 I want copied to sheet2. These ranges are all over the place as a result of how information is copied into the template. For example, the following cells contain info that should flow to the table in sheet2 based on the user selected product (c14, c45, ab123, z34, w43, and several more).

I'm pretty good at figuring out the syntax once I have an example provided. I tried augmenting what you provided to include these ranges but have had no luck so far.

Appreciate your time!
Cheers!

[CatDaddy]

if you could post a sample workbook to clarify what you are talking about i could maybe help you out with a more specific example?

[Odyrus]

I have attached a sample file. There are 2 sheets, template & BP. I would like the user to select a month from the drop down in the template sheet (G2) and then click a button that would run a macro moving relevant information related to BP to the BP tab for the month the user selected. I manually copied over some information to the BP sheet as an example. The macro doesn't need to link to the template sheet, moving over just the values is fine.

Greatly appreciate any help!

[CatDaddy]

where exactly is the data going to on the template worksheet? i see where it should be coming from on B&P but its destination is unclear to me

[CatDaddy]

i think it would look something like this though
[VBA]Sub test()
Dim cell As Range
Dim r As Integer
Dim mnth As String
ActiveWorkbook.Sheets("template").Activate
mnth = Range("G2").Text
For Each cell In Sheets("B&P").Range("A3:A14")
If cell.Text = mnth Then
r = cell.Row
Range(something).Value = Sheets("B&P").Range("C" & r).Value
Range(somethingelse).Value = Sheets("B&P").Range("D" & r).Value
Range(somethingelse).Value = Sheets("B&P").Range("E" & r).Value
'...
Exit For
End If
Next cell

End Sub[/VBA]

[Odyrus]

Data from several external sources is pasted into the template sheet. That data is then used to populate the BP sheet. There are several other sheets just like the BP sheet but I deleted those for the sake of this example.

I inherited this extremely cumbersome process and this is my attempt at making it a little more efficient. The template sheet is my approach to centralizing all the various information that needs to populate the summary sheets (aka B&P). I have to cut and paste the data into the appropriate places on that sheet.

What I'm hoping for, and where I'm getting stuck, is making sure that data is pasted from the template sheet next to the correct month on the roll up sheet based on the choice from the drop down.

As an example, i filled in what the BP sheet would look like if the user chose January and then hit the macro button (haven't created a button yet). Ideally those values would be placed there based on the macro we are discussing.

Hope this provides clarity.

[CatDaddy]

the example i posted will do what you want you just have to fill in the specific destination ranges you want, it would look the same for other worksheets if you wanted to add that in later as well

[Odyrus]

That's great! Thank you!

However, I'm getting a Method 'Range of object '_Global" fail error.

This is what I'm replacing:

[VBA]If cell.Text = mnth Then
r = cell.Row
Range(G7).Value = Sheets("B&P").Range("C" & r).Value
Range(I7).Value = Sheets("B&P").Range("D" & r).Value
Range(H7).Value = Sheets("B&P").Range("E" & r).Value[/VBA]

Should I specify the sheet name, too?

[CatDaddy]

gotta be Range("G7") not Range(G7) etc

[Odyrus]

As Homer would say, Doh!

The macro runs but I'm getting no results on the B&P page. Thoughts?

[CatDaddy]

This would pull data from the b&p page onto the template, i thought thats what you wanted...you are trying to populate the b&p page from the template?