Need help with a macro

V

vlad_cris

New Member
Joined
Dec 26, 2014
Messages
10
Hi,

I need to add a part in a macro that will search in worsheet 2 for a value that is dinamic in workseet 1(search by column A and retrieve column B(email field), likevlookup) but will constantly be on column R and paste it in column AZ in worksheet 1, second part is to copy everything from column AZ a new worksheet (email) that will have all the fields from worksheet 1 but aranged in a friendlier email view

also in the same macro the last part is to add those email addresses in the cc of the email. For example the email addresses will be on column D in the email worksheet and I tried .cc=range(D1:D100) but didn't work :(

please let me know if anyone can help on this, I'll try to upload the macro and the files in order to have a better ideaof what I would like it to look like
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
show us the basic spreadsheet and what you want the output to be

"a picture is worth a thousand words"
 
Upvote 0
vlad_cris,

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


So that we can get it right on the first try:

Can you post screenshots of the two actual raw data worksheets?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post a small screen shot try one of the following:

Excel Jeanie
Download

MrExcel HTML Maker
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here


Or, you can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.
 
Upvote 0
oldbrewer said:
show us the basic spreadsheet and what you want the output to be

"a picture is worth a thousand words"
Click to expand...


Hi Oldbrewer

I have uploaded the excels in Boxnet

https://app.box.com/s/xvalp3k3vipjqzr55lch
https://app.box.com/s/jgz0ns0wtmn4nf8ahkmw

I want to search by column R in the sample sheet and retrieve column B in the macro CA's worksheet. Basically search by column R and copy on column AM in the sample, when running the first part of the macro (No1).
There is already a part in the macro that reorganises the view in order to create a the email worksheet.

Then for the second part (No2) I want to copy column AM on the newly created email worksheet in column D in right next to the client account


I hope that sort of gives you an idea of what I would like it to look like
 
Upvote 0
I never download - sorry

If you copy A1:T10, then come to the reply box here and paste, we will all be able to see it.....
 
Upvote 0
oldbrewer said:
I never download - sorry

If you copy A1:T10, then come to the reply box here and paste, we will all be able to see it.....
Click to expand...

[TABLE="width: 1409"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]instr_no[/TD]
[TD]instr_name[/TD]
[TD]isin[/TD]
[TD]tkur[/TD]
[TD]instr_type[/TD]
[TD]prev_coupon_date[/TD]
[TD]instr_ccy[/TD]
[TD]coupon_date[/TD]
[TD]int_rate[/TD]
[TD]maturity_date[/TD]
[TD]int_accrual_ind[/TD]
[TD]int_basis_n[/TD]
[TD]party_ref[/TD]
[TD]qty[/TD]
[TD]depot_ref[/TD]
[TD]depot_ac[/TD]
[TD]depot_status[/TD]
[TD]ac_officer[/TD]
[TD]int_basis_d[/TD]
[TD]before_date[/TD]
[/TR]
[TR]
[TD]000000342923[/TD]
[TD]XXX CN MOBILE 0941 C/OPT HKD80.666 EXP02.01.15+2[/TD]
[TD][/TD]
[TD]016817095 [/TD]
[TD]OPTI[/TD]
[TD][/TD]
[TD]HKD[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD]02/01/2015 00:00:00[/TD]
[TD]N[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]111111[/TD]
[TD="align: right"]-19520[/TD]
[TD]XXX OTC SG[/TD]
[TD]D001[/TD]
[TD]STR[/TD]
[TD]1177[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]000000342923[/TD]
[TD]XXX CN MOBILE 0941 C/OPT HKD80.666 EXP02.01.15+2[/TD]
[TD][/TD]
[TD]016817095 [/TD]
[TD]OPTI[/TD]
[TD][/TD]
[TD]HKD[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD]02/01/2015 00:00:00[/TD]
[TD]N[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]111111[/TD]
[TD="align: right"]-19520[/TD]
[TD]XXX OTC SG[/TD]
[TD]D001[/TD]
[TD]STR[/TD]
[TD]1177[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]000000342923[/TD]
[TD]XXX CN MOBILE 0941 C/OPT HKD80.666 EXP02.01.15+2[/TD]
[TD][/TD]
[TD]016817095 [/TD]
[TD]OPTI[/TD]
[TD][/TD]
[TD]HKD[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD]02/01/2015 00:00:00[/TD]
[TD]N[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]111111[/TD]
[TD="align: right"]-19520[/TD]
[TD]XXX OTC SG[/TD]
[TD]D001[/TD]
[TD]STR[/TD]
[TD]1177[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]000000342923[/TD]
[TD]XXX CN MOBILE 0941 C/OPT HKD80.666 EXP02.01.15+2[/TD]
[TD][/TD]
[TD]016817095 [/TD]
[TD]OPTI[/TD]
[TD][/TD]
[TD]HKD[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD]02/01/2015 00:00:00[/TD]
[TD]N[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]111111[/TD]
[TD="align: right"]-19520[/TD]
[TD]XXX OTC SG[/TD]
[TD]D001[/TD]
[TD]STR[/TD]
[TD]1177[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]000000342923[/TD]
[TD]XXX CN MOBILE 0941 C/OPT HKD80.666 EXP02.01.15+2[/TD]
[TD][/TD]
[TD]016817095 [/TD]
[TD]OPTI[/TD]
[TD][/TD]
[TD]HKD[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD]02/01/2015 00:00:00[/TD]
[TD]N[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]111111[/TD]
[TD="align: right"]-19520[/TD]
[TD]XXX OTC SG[/TD]
[TD]D001[/TD]
[TD]STR[/TD]
[TD]1177[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
and below is the macro I run

Sub No1_Expiry_Format()


'inputting the formula
Cells(2, 8) = "=IF(R2="""","""",(CONCATENATE(M2,""("",R2,"")"")))"
Range("H2").Copy Destination:=Range("H2:H" & Range("L65536").End(xlUp).Row)

Range("H2:H" & Range("L65536").End(xlUp).Row).Copy
Range("M2:M" & Range("L65536").End(xlUp).Row).Select

Selection.PasteSpecial xlPasteValues


'converting value for column A
Cells(2, 36) = "=value(a2)"
Range("AJ2").Copy Destination:=Range("AJ2:AJ" & Range("L65536").End(xlUp).Row)

Range("AJ2:AJ" & Range("L65536").End(xlUp).Row).Copy
Range("A2:A" & Range("L65536").End(xlUp).Row).Select

Selection.PasteSpecial xlPasteValues


'converting value for column D
Cells(2, 37) = "=value(D2)"
Range("AK2").Copy Destination:=Range("AK2:AK" & Range("L65536").End(xlUp).Row)

Range("AK2:AK" & Range("L65536").End(xlUp).Row).Copy
Range("D2:D" & Range("L65536").End(xlUp).Row).Select

Selection.PasteSpecial xlPasteValues


'converting value for column R
Cells(2, 38) = "=value(R2)"
Range("AL2").Copy Destination:=Range("AL2:AL" & Range("L65536").End(xlUp).Row)

Range("AL2:AL" & Range("L65536").End(xlUp).Row).Copy
Range("R2:R" & Range("L65536").End(xlUp).Row).Select

Selection.PasteSpecial xlPasteValues




'Filter for non today's expiry items
Dim Today As Variant


Today = InputBox("Please input today's date")
If Today = Empty Then
Exit Sub
End If

If ActiveSheet.AutoFilterMode - False Then
Range("A1:AL1").Select
Selection.AutoFilter
End If
Range("A1").Select
Selection.AutoFilter _
Field:=10, Criteria1:="<>*" & Today & "*", Operator:=xlAnd


'Alerting for dates to check
Range("J:J").ColumnWidth = 25
Range("J2:J" & Range("L65536").End(xlUp).Row).Font.ColorIndex = 7

Range("B:B").ColumnWidth = 80
Range("B2:B" & Range("L65536").End(xlUp).Row).Font.ColorIndex = 7




MsgBox "All selection should NOT be today's item. Please CHECK and DELETE. Once deleted and unfiltered, please re-run Option Expiry Format Macro", vbInformation



End Sub
 
Upvote 0
now remove columns that are not relevant to what you need to do and why is every row identical ?
 
Upvote 0
the rows are identical in this case, usually they are different, there was only one entry in this extract so I had to copy it.

the only column I need is the ac officer and there is another worksheet( CA's List) in the macro file I copied below


[TABLE="width: 51"]
<colgroup><col></colgroup><tbody>[TR]
[TD]ac_officer[/TD]
[/TR]
[TR]
[TD]1177[/TD]
[/TR]
[TR]
[TD]1177[/TD]
[/TR]
[TR]
[TD]1177[/TD]
[/TR]
[TR]
[TD]1177[/TD]
[/TR]
[TR]
[TD]1177 [/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 122"]
<!--StartFragment--> <colgroup><col width="61" span="2" style="width:61pt"> </colgroup><tbody>[TR]
[TD="width: 61"]ac officer[/TD]
[TD="width: 61"]email[/TD]
[/TR]
[TR]
[TD="align: right"]1029[/TD]
[TD="class: xl48"]xxx@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]1069[/TD]
[TD="class: xl48"]xx1@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]1177[/TD]
[TD="class: xl48"]xx2@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]1218[/TD]
[TD="class: xl48"]xx3@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]1406[/TD]
[TD="class: xl48"]xx4@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]1424[/TD]
[TD="class: xl48"]xx5@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]1764[/TD]
[TD="class: xl48"]xx6@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]1915[/TD]
[TD="class: xl48"]xx7@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]1931[/TD]
[TD="class: xl48"]xxx@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]277[/TD]
[TD="class: xl48"]xx1@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[TD="class: xl48"]xx2@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]3004[/TD]
[TD="class: xl48"]xx3@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]3024[/TD]
[TD="class: xl48"]xx4@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]3027[/TD]
[TD="class: xl48"]xx5@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]3071[/TD]
[TD="class: xl48"]xx6@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]3078[/TD]
[TD="class: xl48"]xx7@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]3112[/TD]
[TD="class: xl48"]xxx@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]350[/TD]
[TD="class: xl48"]xx1@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]470[/TD]
[TD="class: xl48"]xx2@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]562[/TD]
[TD="class: xl48"]xx3@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]835[/TD]
[TD="class: xl48"]xx4@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]915[/TD]
[TD="class: xl48"]xx5@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]1429[/TD]
[TD="class: xl48"]xx6@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]1521[/TD]
[TD="class: xl48"]xx7@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]1531[/TD]
[TD="class: xl48"]xxx@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]1579[/TD]
[TD="class: xl48"]xx1@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]1581[/TD]
[TD="class: xl48"]xx2@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]172[/TD]
[TD="class: xl48"]xx3@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]2249[/TD]
[TD="class: xl48"]xx4@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]2287[/TD]
[TD="class: xl48"]xx5@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]2395[/TD]
[TD="class: xl48"]xx6@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]2907[/TD]
[TD="class: xl48"]xx7@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]4131[/TD]
[TD="class: xl48"]xxx@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]4174[/TD]
[TD="class: xl48"]xx1@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]4194[/TD]
[TD="class: xl48"]xx2@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]4271[/TD]
[TD="class: xl48"]xx3@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]4445[/TD]
[TD="class: xl48"]xx4@yahoo.com[/TD]
[/TR]
[TR]
[TD="align: right"]4682[/TD]
[TD="class: xl48"]xx5@yahoo.com[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]
[TABLE="width: 122"]
<colgroup><col width="61" span="2" style="width:61pt"></colgroup><tbody>[TR]
[TD="width: 61, align: right"][/TD]
[TD="class: xl48, width: 61"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="class: xl48"][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
VBA Code to Send Mail to skip blank cells
Replies
1
Views
269
Micron
M
M
  • Question Question
I need help with VBA/macro script to collect user input and fill cells
Replies
4
Views
461
mumps
mumps
StephenC101
  • Question Question
Mac Excel macro help needed please
Replies
1
Views
719
jkpieterse
jkpieterse
J
  • Locked
  • Question Question
Still need help on a daily summary sheet
Replies
1
Views
582
Fluff
Fluff
M
  • Question Question
Creating and emailing Excel sheet with only visible data
Replies
0
Views
491
mecerrato
M

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

Share this page

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
Back
Top