# Back Draws

Dec 3, 2008

Back Draws! ...

## Write A Macro Which Draws A Box Around Some Cells

Jan 3, 2009

I am trying to write a macro which draws a box around some cells, problem is that depending on the number of records I want to highlight, the box has to start and finish at different points. Is there any way to write the code so that it refers to a number in the sheet and creates a different size box depending on what the number is?

## How To Create IF / AND Formula Draws Data From Multiple Tabs

May 21, 2014

Is it even possible to create an IF/AND formula that draws data from multiple tabs?

For example,

(assume there are tabs named exactly the same as each of the data entries in column A)

IF A2 = "ARI" then D2 = ARI!H3. Is this possible?

## Calculating Distances Between Back-to-back Locations

Aug 11, 2012

I have a layout something like the following:

A1
A2
A3

[Code]....

Where each (i.e., A1) represents a location. I have tried to use a coordinate system but this will not work for the back-to-back locations. (Assuming each location is 2 feet wide, For example A1 to C1 is 4 feet apart, not 2 feet (as Euclidean or rectilinear would calculate it as).

Would there be a way to incorporate an if statement for those locations that are back-to-back? As a rectilinear distance calculation would work as long as the locations are not part of the same "block".

Ultimately I am looking to have a matrix which contains all the distances between each location:

A1
A2
A3
B1

[Code].....

## Copy The Current Price Back To Sheet1. The Current Price Needs To Be Pasted Back Into Sheet1 (next To The Existing Price)

Jul 24, 2006

All data is located within one book. I have two sheets with material codes in each sheet which include pricing (existing and current)

Sheet1 (has existing material codes plus existing pricing) Has about 1200 lines
Sheet2 (has current material codes plus current pricing), has about 36000 lines

I need to cross check if the material code (taken from sheet1) are still available in sheet2, and if they are, copy the current price back to sheet1. The current price needs to be pasted back into sheet1 (next to the existing price). If the material code doesn't exist (for whatever reason, in sheet2), the program needs to move onto the next line and leave the current price for that material code blank. The program should finish once all the lines in sheet1 are completed. I have attached a sample of what I'm trying to do,

Dec 26, 2008

I have a spreadsheet with some scripting that automatically emails the report to whoever is on the sheet. I'm not sure what kind of scripting it is. I think my boss found it somewhere.

Whenever I scroll either down or up, the first 3 rows disappear and I'm not able to scroll back up. The scrollbar shows that I'm not at the top, but it won't let me go back up. The first 3 rows contain the button to send the email. If I close out without saving and open it again, its fine, until I scroll up or down.

## Back And Forth Between Workbooks

Feb 2, 2007

Copying information from various sheets from one workbook to paste into similar sheets in another workbook? It would involve switching back and forth between workbooks.

## One Hundred Back To 100

Sep 26, 2008

I found a way (on this board) to spell out numbers...I.E.: 140 = "One Hundred Forty" .

But is there a way to convert "One Hundred Forty" to 140 and format as a number or general or pretty much anything but text? I thought would be as easy as Cell Format > Number

## IF And Vlookup Functions Which Comes Back Yes Or No.

Sep 20, 2007

I have a spreadsheet with over 20,000 rows. From another spreadsheet I need to find if any of a list of 90 customers are within the first, using a simple IF and vlookup formula which comes back yes or no.

I am using: =IF(A2 = VLOOKUP(A2,ihcust!B:B,1),"YES","NO")

However, although this works when they are spelt exact in both sheets if they are not it will say no.
eg. If I was Looking up "Joe's Icecreams" from the list of 90 but in the other sheet it is listed as "Joe's Icecreams (admin)" it would not recognise it and come back no.

Is there away to get around this problem

## Making IF= Formulation Come Back As N/A

Nov 2, 2009

I am trying to create an IF formula so when i try to get the percentage change if lets say the 2008 number was 0 and the 2009 number increased by 9 than i would get an error so i want the if formula to come back as n/a.

## Go To Another Worksheet And Then Come Back To Original

Aug 21, 2014

I'm setting up a payroll for several employees in one workbook. I start from the particular employee's worksheet and then I need to go to another sheet where various information is stored and then come back to the original employee's sheet. The current macro works fine if the various information is stored on each employee's worksheet but I can't figure out how to go to another sheet and then come back to my active cell in employee sheet.

[Code] .....

## Back Up File On Save

Jul 2, 2009

I am currently using this code which automatically saves a back up copy to another location on my system.

However, the only way i can get it to work is to assign it to a command button. Is there any way in which this will work once i just click the save button.

## Workbook_close To Back-up File

Dec 7, 2009

I am currently using this method to create a back-up of the current workbook to another location using a yes/no message box.

If the user clicks no, the workbook will be closed...also if they have not saved any changed data excel also gives them this option in case they close by mistake.

Its the yes option i am having issues with.

If they click yes...the back-up code runs fine and saves the file elsewhere...but for some reason the code loops and runs again. Not sure why this is happening but i do not want it to. Also again if there is any unsaved changes...excel does not give them the option to save this first...i would like this happen.

## Count Back X No. Of Cells

Aug 18, 2008

I want to write a formula that counts back a given no. of columns and averages those cells.

Example:

cell P4 - 4 = cell L4
AVERAGE cell P4 to cell L4

To obtain the number of cells I am going to look back I will be using a VLOOKUP... So that number will be open to change.

## Make #NAME? Go Away And Calculation Come Back

Jan 1, 2010

=IF(OR(J4="",K4=""),"",NETWORKDAYS(J4,K4,Holidays!Z29:Z39)-1)

I have this formula in Column L. The calculations are working fine and I close the file. After I email the workbook from one computer to another and then resave it, every row in Column L that has the formula turns to #NAME?

Do you know why this is happening?

## Go Back To Col A Pressing Enter?

Jun 23, 2012

Once I reach Column G and I press enter, instead of going to Col I, How can I get Excel to back back to Col A, but on the next row?

## Column A Disappeared - How To Get It Back

Jul 24, 2012

Column A disappeared and didn't come back. I even arrow over to the left margin, but the furthest it goes is to column B. How can I get my column A back?

## Add Back Button To Input Box?

Nov 14, 2013

I have made a sheet with 10 input boxes, some for text and some for numbers. If you make a mistake now you must click though the rest of the questions to start again and make a change. Is there a way to add a back button that would take you to the last question? Or possibly make the cancel button stop all questions? Here is a sample of the code

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim strname As String

[Code].....

## How To Add Back Number To Itself Throughout Range

Aug 2, 2014

I am a new to VBA. I want to add a range of numbers to another range of numbers. However second range should always add to itself with first corresponding array values. For example my firsr array is A1:F10 and second array is A15:F25. Now i want A15 = A15+A1, A16= A16+A2, A17= A17+A3 and so on. Every time second array should retain itself value and get added with first corresponding array values.

## Giving Back Focus

May 10, 2007

I've got a Macro that generates three reports for me, one at a time.

After a report has been generated it creates an Email and attachs the report to the Email, I then check the report is ok before sending out.

The problem is, that once the first Email has been generated Excel is pushed into the background and the macro sloooooooows down.

I believe (though I will quite happily be wrong) this is because Excel is no longer the PC's main concern.

What I was wondering was, is it possible to bring Excel back to the foreground once the Email has been generated.

## Add A Total To The Spreadsheet It Comes Back As Zero

Dec 4, 2007

I have cut and paste a series of numbers from my online bank account statement, however, when I go to add a total to the spreadsheet it comes back as zero. I have removed the currency sign from in front of it, I have changed the column format to be numbers but the total still reports a zero.

However, if I type in the number the value is recognized.

## When Returning (Using Back Button) From Www

Jun 24, 2008

I'm finalizing an Excel app - which uses Userforms only. On opening the app I use the code line Application.Visible = False

After clicking on my Label2: (I'm taken to the Web site, but when I click the
< (Back button - Return) I am returned to my Userform BUT my Excel App (Menus, etc) is now showing up. How can I keep it "turned-off"?

Private Sub Label2_Click()
On Error GoTo TheEnd
Exit Sub
TheEnd:
End Sub

## Go Back To Previous Cell

Sep 25, 2008

With "edit directly in cell" turned off, if you double click through in a cell containing a formula to the cell being refferenced, is there a quick way of getting back to the original cell you clicked on?

## Formula Back References

Mar 1, 2009

Is it possible to make a back-reference in a cell formula similar to regular expressions?

Example:

=IF(I15=FALSE, [H17 * INDEX(Percentages,I17+1)], IF(A17="", \$1, 0))
...where [ ] indicates the actual reference and \$1 indicates the reference number

## Moving Back And Forth Between Files

May 12, 2007

I am developing a macro where I need to open a file, copy a value from a cell in that file and paste it in a cell of the original file. I need to do this several times from the same two files. My problem is that I don't have a command that moves back to the newly opened file to copy another value and paste it, again, in a cell of the original file. I can't use a specific file name to move to the newly opened file because the new file name changes every day. Here is the command that I use to open a new file:

Dim fn As Variant
fn = Application. GetOpenFilename("Excel-files,*.xls", _
1, "Select The Previous Day's Borrowing File To Open", , False)
If TypeName(fn) = "Boolean" Then Exit Sub
' the user didn't select a file
Debug.Print "Selected file: " & fn
Workbooks.Open fn

I need to move back to file fn several times, but, as I stated above, the file fn changes every day.

## Always Create Back-up Automatically

Aug 15, 2007

How to make sure that every workbook I create has the option of "backing up before saving" turned-on? I need a Excel-wide option, i.e. one that applies to all workbooks created, The known to me facility of turning the option on every time I create a workbook is not satisfactory because often I forget to turn the option on when creating a new book -- and I create many.

## Conditional Formatting For Back Order And Already Due

Apr 1, 2014

If back Order if morethan 0 and due date is < today "over due" will appear. and if backorder more 0 due date is today, "due today" will appear.

## Formula Keeps Bringing Back Offset?

May 30, 2014

Here is my formula:

=IFERROR(INDEX(Stored!\$D\$22:\$AN\$795, MATCH(D11,Stored!\$E\$22:\$E\$795,0), MATCH(E10,Stored!\$C\$22:\$AN\$22,0)),0)

For some reason it keeps pulling back the value that is one cell to the right, so one column off.

What could it be.

In this example it should pull back N363 and it is pulling back O363.

## How To Send Object To Back So That Cells Appear On Top

Apr 25, 2014

I am using Excel to fill in information for W-9 tax documents. I have formulas on one sheet that pull from other sheets where the numbers for the form are entered. Currently we have to put the actual form in the printer. I'd like to embed the form into excel so that the numbers pulled by the formulas and the form itself all print. The problem I'm having is I can not get the form to sit in the background. The cells are hidden behind the form and do not show up when printing.

## Mapping The Data From Back Of The Column

Jan 22, 2009

I am using the Formula of Index here as i have a sheet names with Details and mapping i have a company name for which i have to map the policy no from the details sheet. The Thing is i will not use Vlookup i need a formula of Index which maps the data from the backwards range. Attached a excel sheet.