PDA

View Full Version : Solved: Looping



Odyrus
07-02-2012, 09:21 AM
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
07-02-2012, 09:25 AM
For each cell in Range("A3:A14")
If cell.value = myvalue Then
'copy/paste some data
End if
Next cell

Odyrus
07-02-2012, 09:26 AM
I do believe I was WAY over thinking this one!

Much appreciated CatDaddy!

Cheers!

CatDaddy
07-02-2012, 09:36 AM
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
07-02-2012, 01:00 PM
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:
ActiveWorkbook.Sheet1.Range("g2") = myvalue

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

CatDaddy
07-02-2012, 01:04 PM
how far have you gotten on your own? can you post your code?

and you would want
myvalue = ActiveWorkbook.Sheet1.Range("g2").value

CatDaddy
07-02-2012, 01:10 PM
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

Odyrus
07-03-2012, 05:32 AM
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.

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

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
07-03-2012, 11:07 AM
did you try the code i posted?

Odyrus
07-03-2012, 11:42 AM
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
07-03-2012, 11:56 AM
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
07-03-2012, 12:13 PM
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
07-03-2012, 12:43 PM
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
07-03-2012, 12:50 PM
i think it would look something like this though
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

Odyrus
07-03-2012, 12:57 PM
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
07-03-2012, 12:59 PM
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
07-03-2012, 01:04 PM
That's great! Thank you!

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

This is what I'm replacing:

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

Should I specify the sheet name, too?

CatDaddy
07-03-2012, 01:09 PM
gotta be Range("G7") not Range(G7) etc

Odyrus
07-03-2012, 01:13 PM
As Homer would say, Doh!

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

CatDaddy
07-03-2012, 01:21 PM
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?

Odyrus
07-03-2012, 01:23 PM
Yes, The B&P page gets populated from the template. So sorry if I was confusing!

CatDaddy
07-03-2012, 01:27 PM
Sub test()
Dim cell As Range
Dim r As Integer
Dim mnth As String
mnth = Sheets("template").Range("G2").Text
ActiveWorkbook.Sheets("B&P").Activate

For Each cell In Range("A3:A14")
If cell.Text = mnth Then
r = cell.Row
Range("C" & r).Value = Sheets("template").Range(something)
Range("D" & r).Value = Sheets("template").Range(something)
Range("E" & r).Value = Sheets("template").Range(something)
'...
Exit For
End If
Next cell

End Sub

Odyrus
07-03-2012, 01:37 PM
Works perfectly; I owe you a beer.

I really appreciate your time and patience! Thank you! :clap:

CatDaddy
07-03-2012, 01:39 PM
if i am ever in ireland i will take you up on that :)

mark the thread as solved using thread tools button, and to run the macro from a button all you have to do is add the button and this code in the worksheet module:
Private Sub CommandButton1_Click()
Call test
End Sub