Hello everyone,
I apologize if this has already been addressed in another thread but, I wasn't able to find it.
I am trying to create a macro to copy cells B5:B10, B12, and B15. B10 may have an n/a return depending on other info on the worksheet. Once it is copied, I will be using this to create an email (using vba) to send from outlook.
I would appreciate any help anyone is able to provide, even if it's a "nope, can't do that" response.
Thanks!
It will help a lot if you could supply a sample workbook without sensitive data. It's certainly doable, but it will help if we could actually see the issue.
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
Sorry about that. Please see attached sample. Entering 1234 in B4 will return "F" in B10. Entering 4321 in B4 will return "#N/A" and if "#N/A" is returned it should skip copying this cell. Thank you!
Why do you need a code while a formula with iferror will do? You need to include iferror (>=excel 2007) if there is no match.
Put this formula in B5 and drag it down
Please Login or Register to view this content.
Hi AB33! That works for the first row of values that ties with 1234 but, how do I get the values to return for 4321? The actual spreadsheet that I am working on is almost 1,000 lines. If I don't have to use VBA, that would be awesome! Thank you!
Well it is like any match function, you specify the column or row you wish to return. Imagine, if this was to return from a column, you specify the column Number. Since you are returning from a row, you change it from row 2 to 3.
I also see your point, you do not want to manually adjust the rows for all 1000 rows.
=IFERROR(INDEX(Data!$B$3:$P$3,MATCH(A5,Data!$B$1:$P$1,0)),"")
Unless of course, you want to do multiple return, you have to go through VBA route, but I believe you can auto rows instead of hard coding $B$3:$P$3. I am sure there are people who can came up with a formula as well, but you need to post it on the formula section. My formula set of mind has gone down since I have focused on coding.
I need to understand what are you try to get.
My job requires that certain people are emailed in certain scenarios. In some of these scenarios, there is a person in B10 and sometimes there isn't, it depends on the market. What we enter in B4, then looksup to the data tab and tells us who to email. My ultimate goal is to have a macro generate the Outlook email (have code for this) based on the people in B5:B10, B12, and B15 however, if B10 is #N/A this will error in Outlook. So I'm trying to figure out how to get rid of the #N/A and I apologize if I'm over thinking this and maybe a formula is the route to go. If it is, please let me know your thoughts and I'll close this out. Thank you!
Well, that is easy. If these data are somehow linked to e-mail, I presume you are going to loop through the range and you are worried that if a cell value is #NA, it will error your code on outlook.
There is a VBA function which skips a line (cell or row), if there is an error.
Let say B10 = #N/A, then you could do if not iserror(Range("B10") then.
This line will skip that cell.
You also need to go back to the source of the error and use iferror to remove #N/A. If you look at the formula, B10 no longer shows an error, but blank, so when you do your code in outlook, the code will not fail as the cell has a blank value.
I hope it makes.
Sweet! Thanks AB33! Have a great day!
Hi sidewaysw ( and AB33 )
@ sidewaysw
I think AB33 has you sorted, but I started so I finished...
I am still not clear on what you want exactly, so let me at least give you what appears to tie up close with what you said... (..and I bet it is not what you wantOriginally Posted by sidewaysw
..),
I read from that Copy cells in those three ranges that are not errors.
Code here
http://www.excelforum.com/showthread...=9#post4521758
Brief description of code:
Rem 1
I assume a range like this, as you gave_..
Values
Using Excel 2007 32 bit
Worksheet: Sheet1
Row\Col O N M L K I H G F E
Formulas
Row\Col =VLOOKUP(B4, Data!A:B,2,FALSE) =VLOOKUP(B4,Data!A:C,3,FALSE) =VLOOKUP(B4,Data!A:D,4,FALSE) =VLOOKUP(B4,Data!A:E,5,FALSE) =VLOOKUP(B4,Data!A:F,6,FALSE) =VLOOKUP(B4,Data!A:H,8,FALSE) =VLOOKUP(B4,Data!A:I,9,FALSE) =VLOOKUP(B4,Data!A:J,10,FALSE) =VLOOKUP(B4,Data!A:K,11,FALSE) =VLOOKUP(B4,Data!A:L,12,FALSE)
_.. and i am going on what you said..”.. Copy .... B5:B10,
B12, and
B15 .... “
‘1b) I make a helper column And put the values in. This allows me to use in
‘1c) the special VBA Mental Cells Method to get at the Text values while disregarding the Error values.
‘1d) This creates the Range Object necessary to perform your requested “Copy”
I am using the Union method to create the Range based on combining the rectangular Areas of the ranges you asked for. I have allowed for the some possible combinations depending on which ranges may be empty as would be the case if all cell values in that range were Error values. I have not done all combinations – I leave that to you – you should get the general idea.
Rem 2) Your wanted “Copy”
‘2a) Another helper column as the clipboard is a bit insane and will paste out your copied Range in a single continuous column, but the text includes that from your entire used Range. I have no idea why that is and I doubt if anyone does!
‘2b) Your Copy
‘2c) uses the helper column to get just the text from the required Copied range into the clipboard.
Rem 3) I did this for fun to get your text values as a single string. – I have no idea about sending things per Email with VBA, but I guessed a single string of the values might be handy for that.
Rem 4 A simple demo of your string with a message box.
Rem 5 Optional to delete the two helper columns. I ‘commented it out as you may find it useful to have those.
I have run out of Post size space – so on to the next post_... ......
Last edited by Doc.AElstein; 11-24-2016 at 10:34 AM.
'_- Google first, like this _ site:ExcelForum.com Gamut
Use Code Tags: Highlight code; click on the # icon above,
Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
http://www.excelforum.com/the-water-...ml#post4109080
https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )
_....from last post
If you run the code on your given test data_... first you get the two helper columns:
Using Excel 2007 32 bit
Worksheet: Sheet1
Row\Col O N M L O K N H M E L K I H G F E
_... then a message box pops up:
MsgBoxswsw.JPG
http://imgur.com/3nhyTVT
Attachment 488915
Alan
' Rem Ref
' http://www.excelforum.com/showthread...t=#post4398645
' http://www.eileenslounge.com/viewtop...181736#p181655
' http://www.excelforum.com/excel-form...ml#post4519556
' http://www.mrexcel.com/forum/excel-q...ml#post4043472
' http://www.mrexcel.com/forum/excel-q...ml#post4375560
' http://www.mrexcel.com/forum/excel-q...ml#post4039255
' http://www.mrexcel.com/forum/excel-q...ml#post4043472
' http://www.eileenslounge.com/viewtop...193871#p193871
' ' http://www.eileenslounge.com/viewtopic.php?f=27&t=22512
' http://www.excelforum.com/showthread...t=#post4414189
' http://www.excelforum.com/showthread...95#post4256195
' http://www.mrexcel.com/forum/excel-q...ml#post4043472
' http://www.mrexcel.com/forum/excel-q...ml#post4071766
' http://www.excelforum.com/excel-prog...ml#post4504411
' http://www.excelforum.com/tips-and-t...explained.html
' https://app.box.com/s/lts3h2ejf33yj2i2xmaq5zrq784qwwl9
' http://www.excelforum.com/showthread...95#post4256195
' http://www.excelforum.com/showthread...=1#post4483344
' http://www.excelforum.com/showthread...t=#post4414189
' http://www.excelforum.com/showthread...95#post4256195
' Variant and errors
'The value Empty denotes a Variant variable that hasn't been initialized (assigned an initial value). A Variant containing Empty is 0 if it is used in a numeric context and a zero-length string ("") if it is used in a string context.
'Don 't confuse Empty with Null. Null indicates that the Variant variable intentionally contains no valid data.
'In a Variant, Error is a special value used to indicate that an error condition has occurred in a procedure. However, unlike for other kinds of errors, normal application-level error handling does not occur. This allows you, or the application itself, to take some alternative action based on the error value. Error values are created by converting real numbers to error values using the CVErr function
' TMS http://www.excelforum.com/excel-new-...ml#post4017211
Last edited by Doc.AElstein; 11-13-2016 at 11:24 AM.
Alan,
If there is an error on a cell, VBA will error if you test that cell, unless of course ,you include an error trapping line. In effect, VBA skips that line and loops in to next line. Having said that, there is a better way of handing the issue at hand; that is, try to tackle the source of the error at source. since this error usually arise in formula, you could use excel's built-in if error function.
Hi AB33
Thanks for that
I think I follow what you are saying_...
That is useful stuff to keep in mind. I do not have much experience with Excel Spreadsheets and errors.
In VBA I know there is a difference between if a code line Errors , needing Error handling, or if an "Error"" Value" is returned , which you can "catch" in for example a Variant variable, vTemp, which can hold an "Error". You can then test for that with the like
__If IsError(vTemp) Then
or change the vTemp to a string of it with
Cstr(vTemp) then check for the specific Error text string you may be expecting
http://www.excelforum.com/excel-new-...ml#post4017211
-.....
My Code does not , I think, concern iteslf directly with Errors, or rather the Method I use is not adversely effected by them_...
_....My code uses the VBA Special Cells Method ( with argument (xlConstants, xlTextValues)) , to skip cells with errors. It only "takes" the "Cells" with Text values in them. ( I guess "internally" the code which organises the Specilal Cells stuff does error handling or whatever ).
( Incase all cells in a range are errors then that would error so I have error trapping in the code to catch that.). If there are at least some cells in the range with Text values that are not "error" Values , then you get a valid range returned with Special Cells Method even if there are cells with errors "values" in them.
Alan
Last edited by Doc.AElstein; 11-12-2016 at 04:35 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks