VBA Relative Value Copy, New User Need Help

mattp24

New Member
Joined
Jul 24, 2007
Messages
5
Hello I am a new user to VBA and need some help learning how to create a macro that searches for a value and then copies and pastes the data associated with that value on a new worksheet.

On worksheet 1 I have the bloomberg list of ticker data, which is consistently updating.

here is what the data looks like on worksheet1, each line is a new row:


C D E F G H
Ticker name
data data data data data
data data data data data
data data data data data

Ticker name
data data data data data

Ticker name
data data data data data
data data data data data



On worksheet 2 I want to be able to type in a ticker name and then click the macro. The ticker names are in all caps and three letter long (i.e. MMM)

1. So I want to type in a ticker name in worksheet2 cell B2
2. Then click my macro
3. The program search for the ticker name on worksheet1 column C
4. The program copies the data associated with the ticker name
5. The program pastes this data onto worksheet2 cell B4.

I understand this may be very complex, but if anyone has any ideas I would be extremely grateful. Thank you.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Try this macro

Code:
Sub test()
Set WS1 = Sheets("Sheet1")
Set WS2 = Sheets("Sheet2")
x = WS2.Range("B2").Value

'clear current contents
LR = WS2.Cells(4, "B").End(xlDown).Row
If LR = 65536 Then LR = 3
WS2.Range("B4:H" & LR).ClearContents

On Error Resume Next
BR = WorksheetFunction.Match(x, WS1.Columns(3), 0) + 1
If BR = "" Then
    MsgBox "Not Found"
    Exit Sub
End If

ER = WS1.Cells(BR, "C").End(xlDown).Row

WS1.Range(WS1.Cells(BR, "C"), WS1.Cells(ER, "H")).Copy WS2.Range("B4")

End Sub
 

mattp24

New Member
Joined
Jul 24, 2007
Messages
5
Yeah that works awesome thanks. One last questions, do you know how I would write an if statement in the same macro saying that if the data retrieved was greater than 11 lines put Yes in B28, otherwise put No?

Here is what I have i dont think it is right though.

If Count(Worksheets("sheet2").Cells("B5:B17")) > 11 Then
Worksheets("sheet2").Cells("B28").Value = "Yes"
Else
Worksheets("sheet2").Cells("B28").Value = "NO"
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Try

If Worksheets("sheet2").Range("B5:B17").Rows.Count > 11 Then


But to be more specific to your macro, it would be

Code:
If WS1.Range(WS1.Cells(BR, "C"), WS1.Cells(ER, "H")).Rows.Count > 11 Then
 

Forum statistics

Threads
1,181,658
Messages
5,931,270
Members
436,785
Latest member
KingGideon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top