Free Microsoft Excel 2013 Quick Reference

Creating Inventory Macros

I am using Excel to keep track of monthly inventory and I need to
input stock on daily basis. Column A has a 6 didget stock numbers
approx. 300 items and I have an in & out column for each day of the
month. I need to remain in one column while I find the stock no.
according to the day.

Ex.

#112233 input 2 to day March 7

#112233 being Column A98, March 7 being column Q entered into Q98 in 2
pcs.

Stock no. #2666 A56 entered to Q56. Each time I enter a stock no. the
highlited cell will remain in column Q until request to change day of
month.

Need macro to enter date, another to find stock cell.

Currently using macro below but offset would not be used because
offset continuously changes daily.

Sub Macro1()
Dim ans
Dim cell As Range
ans = InputBox("Enter Stock number")
If ans <> False Then
Set cell = Cells.Find(What:=ans, _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not cell Is Nothing Then
cell.Offset(0, 4).Select
End If
End If
End Sub

Thanks,
Rube


I am using Excel to keep track of monthly inventory and I need to
input stock on daily basis. Column A has a 6 didget stock numbers
approx. 300 items and I have an in & out column for each day of the
month. I need to remain in one column while I find the stock no.
according to the day.

Ex.

#112233 input 2 to day March 7

#112233 being Column A98, March 7 being column Q entered into Q98 in 2
pcs.

Stock no. #2666 A56 entered to Q56. Each time I enter a stock no. the
highlited cell will remain in column Q until request to change day of
month.

Need macro to enter date, another to find stock cell.

Currently using macro below but offset would not be used because
offset continuously changes daily.

Sub Macro1()
Dim ans
Dim cell As Range
ans = InputBox("Enter Stock number")
If ans False Then
Set cell = Cells.Find(What:=ans, _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not cell Is Nothing Then
cell.Offset(0, 4).Select
End If
End If
End Sub

Thanks,
Rube

The worksheet that I have has several tabs along the bottom;
Report/Report2/Report3 etc…/Deliveries/Credits/Weekly Inventory/Recap What I
am trying to do is create a macro that makes a new Report tab daily, I have
done the Macro that creates the daily new report tab but the macro I have
created always copies the original tab. I would like to find out a way that
this macro will always create a copy of the latest report tab.

I have been trying to Create a macro to copy variables from different columns
but seem to be falling short as the variables that need copying are from different columns.
there is approximately 50 so do i have a time limit.. and they are the result of equations, so would this affect them
does it record my copying and pasting ? or do i need to program .

heres a simple example to work off in attachmentsBook3.xlsx

Thank you for this forum. This is my first question, so I appologize if it is not perfect. Please let me know if you need more info.

I am using Excel 2007.

I have a group of large databases that were misconfigured, and now I need to edit the formula's in each cell. There are more than 12,000 rows of 6 collumns each, on up to 11 sheets per workbook, and 8 workbooks that I need to fix. Within a workbook, there are up to 42 different formula's that need to be replaced. I know how to do this with the built in Find/Replace, however that is too much work, so I want to create a workbook level VBA macro to do this for me. The problem is that I am just beginning to learn how to create VBA macro's, so I don't really know how to do this.

Here is an example of what I need to do. On all sheets within the workbook, I first need to replace the formula in any cells that contain the formula "=A4" with the formula "=B1". Then I need to replace the formula in any cells containing formula "=A5" with formula "=C1". This continue's until formula "=A45" is replaced by formula "=AQ1". Please help me to create a "Sub FindAndReplaceFormula()"

Thank you for your kind assistance

JMDIGS

Hi I am very new to excel and am wanting to run a command line from a button within a sheet. I need to create a macro that will run the command line (transferring the file to another location) and save at the same time. I already have the save function in place but I need help with running the command line. Can any one help me with this please??!!

Thanks!!

I need to create a macro that will automatically hard code the bloomberg function so that it can be seen by any computer in my network.

My question is, how do I create a macro that will allow me to download data from bloomberg (Specifically corn and other commodity prices) onto a spreadsheet that already has previous price data on it? The rows are the dates and the columns are the prices. It has already been filled out until recent dates.

I hope this is specific enough, If not please let me know what other information would be helpful.

Hi,

I need to create a macro to search for a value or certain conditions to the value entered in a particular cell. The search will be made down a column and when the data corresponding to the value or condition is found, I would like to highlight the particular row with color.

For instance, entered value in cell A1 is 240,
i would like to search down column D row 1 to row 100 for the first value that is equal to or exceeds the value in cell A1 (ie. 240). Once the value is found, I would like to highlight the particular row.

Appreciate if someone is able to help. Thanks very much in advance.

I have never created a macro.

How do you create a macro that will automatically hard code the Bloomberg function so you can see the data from any computer?

Hello. I was wondering if it is possible to create a macro that will evaluate cells sort of like an If statement?

What I am attempting to create will allow me to have the macro evaluate a range of cells.

Ideally it will evaluate cells C18:C29 starting at the top. For example, it will say if cell C18 is greater than zero then place the corresponding cell which is B7 from Sheet 2 into the first available cell in range B5:B14 on the current sheet, Sheet 1. If it is equal to zero then it will do nothing and move on to C19 to evaluate it the same way.

C18:C29 correspond to B7:B18

Any help would be much appreciated. Hopefully I used a valid Keyword Title.

Hi, I would like your help if possible. I have a table in excel (10 x 4). I want to create a macro instruction in which it will search the excel, it will look row from A1 until A10 and replace each cell to a different value that I want.
For instance lets say that in cell A1 we have "Ilias" in cell A2 we have "John" and so on until cell A10. I want the macro instruction to search and replace A1 to "George", A2 to "Maria" and so on.

Can someone help me please ?

Trying to create a macro which would plot the data highlighted in
temperature vs distance.

distance is the one highlighted in green
temperature is on the one highlighted in red
i woudl like to space the temperature in increments of an hour which is highlighted in blue.

Hello All,

I created a form which allows users to open up a report (by using a macro). However, I have been requested to give users several options on how this report can be opened. For example; besides seeing all the reports for the complete year they would like to open the report by date range (four different quarters).

I am not an expert in Access, so for me to do this I would copy the query and report four times I would then create four macros to open up each report (for each quarter). However, this would flood my database with the same report. Is there a way to create a macro and use that one to open the report in five different time periods with five different command buttons? Or is there a different route I can take? I know I can create a text box to enter a start and end date, but my boss wants five different buttons so no date input is needed.

This is my form: (each time frame equals a command button)

First Quarter
Second Quarter
Third Quarter
Fourth Quarter
Complete Report FY 2005

Note: Each command button would open up the same report, but in a different time frame.

Hopefully I am explaining this clearly.

Thanks!!!!
Lisa

I have created a macro that manipulates some data in excel. Once the data has been manipulated I open up Microsoft Word and run Mail Merge, and point the database to the excel file.

I would like to automate this if possible with one macro. How would I take my existing excel macro one step further to include opening Microsoft Word and running mail merge?

Has anyone created a macro In Visual Basic itself under Tools/Macro/Create.

I have the need to print a userform in landscape. I have seen posts stating the use of printscreen.

In the toolbar under File/Print - options are given to select range(either current module or project),print what(form image and or code), and even print set-up options which allow for paper size selection, orientation, and printer properties.

Couldn't a macro be made to do this?

Thanks,

Kurt

Hi all,

I need a macro that can create new macros. I want to be able to click on a button that will make a new button with a new macro assigned to it each time it is hit. I can make all the buttons and such I just don't know how to write code that will make a new module with code in it.

Any help is much appreciated!

Thanks

I need to create a macro that will compare two spreadsheets (two separate
documents); looking at two columns for matching numbers. When the number is
identical, the macro will copy the cells from that row in the first document
to the end of the row of matching data in the 2nd spreadsheet, then continue
through the remainder of the file.

Example: File 1 has column C = File Number. File 2 has column E = File
Number. Macro reads File 1 first file number, looks at File 2 and locates a
row with that information. It then grabs the cells from the row in File 1 and
pastes them into empty cells after the data in File 2. It goes through that
for every row in File 1 and reports back that it has completed xxx rows.

Any and all assistance will be greatly appreciated.

Hi there!
I'm hoping someone can help me. I think this is relatively easy I just haven't got my brain switched on to do it!
I want to create a macro button that counts and increments by 1 every time it is clicked. After clicking on it x amount of times I want to be able to reset the value and start again. The previous amount of total clicks needs to be stored before resetting and starting the counter again. Hope this is clear.
Help would be so gratefully received.
Thanks in advance!!!

Hi
I need an help to create a macro to copy the value from number of excel sheets and paste it into another single excel sheet.
I have number files which are located in one location (ex: test, test1, test2, test3, test4)
I need to open each file and copy the item number which is in Cell B2 in the file named test.xls and paste it in the FINAL workbook and then again copy Total_Liab value in the tab named Calculations in the same workbook test.xls (total liability value) which is the last cell value in the column Q (example: Q51 in the Calculations sheet) Q51 and paste it next to the same item number in the FINAL workbook. Once it perform the action it should close the test.xls worksheet and then open the next test1.xls worksheet and should follow the same procedure as explained for the test.xls. Could you please help to create an macro

Hello,

I am trying to create a macro to use on a speadsheet that checks the value of a cell in Column A and if it meets a criteria (which is variable) then it will delete the entire row.

The contents of the cells is below. The "Pc. Price:" part does not change, however the price does. The price can be from a whole number with no decimal point to having thousandths of a cent (i.e. .003).

The Pc. Price, Total Value, Total Pieces, Total Cartons, and Delivery address are the only things I want to be deleted. I can not figure out how to delete a line based on part of the cell value. The information is electronically fed to me so I have no choice in how it shows up on the excel worksheet. (I tried copy and paste into excel to hopefully pull the information apart, however last time I tried that it all went into column A anyways.)

Each row of information below is in 1 cell together, the decription of the value, and the value.

Pc. Price: 0.03
Total Line Value: 300
Line I want to Keep
Line I want to Keep
Line I want to Keep
Total Pieces: 10000
Total Cartons: 100
Line I want to Keep
Line I want to Keep
Delivery to: adress with letters, numbers, and dash's possible
Line I want to Keep

I've recorded a macro, however when I go to delete the lines it deletes the same row numbers everytime. I go through about 20 of these sheets a day, and the data for each part can be from 17-19 rows long, and there can be up to 200ish groups of the 17-19 lines of data on each sheet. So that didn't work out to well for me . Any help would be appreciated. Thanks!

Hi,
I want to create a macro for time. so that when i click the the button of time then the active cell which is having the cursor should get the present time in that cell.
And how to create the time button so that whenever the cursor move over it the arrow should be changed to the hand.

Thanks in advance....

I need to create a macro that will move information from a range of columns and then insert them under existing information in another column.

here's my example, this is what I'm starting with:

There are only 3 rows in this worksheet and I have numbered the columns just for reference in solving this problem: Anyway, this is how it looks when I import the records:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 First Last Offence Retained 1st. AMT PMT Method Receipt # Bal Fwd Date AMT Pmt Method Receipt # Bal Fwd Date AMT Pmt Method Receipt # Bal Fwd Date AMT Pmt Method Receipt # Bal Fwd Ashley Brown Speeding 0 to 15 over 04-Mar-08 100 Cheque A4545 267.5 #### 150 Debit CIBC 456 118 #### 75 Cheque CIBC 78 43 ######## 43 Cheque CIBC 88 0 Frank Hiller Stunt--Speeding 02-Jan-08 100 Cash cash 267.5 #### 50 Cash cash 218 #### 100 Cash cash ## 02-Apr-08 118 Cash cash 0 jane doe Speeding 30 To 49 over 14-Feb-08 50 Cheque 23232 317.5 #### 50 Cheque TD 023 268 #### 100 Cheque td32 ## ######## 168 Cheque td35 0

And this is how I need it to look after the information is moved and re-inserted:

1 2 3 4 5 6 7 8 First Last Offence Date 1st. AMT PMT Method Receipt # Bal Fwd Frank Hiller Stunt--Speeding 02-Jan-08 100 Cash cash 267.5

19-Feb-08 50 Cash cash 217.5

06-Mar-08 100 Cash cash 117.5

02-Apr-08 117.5 Cash cash 0 jane doe Speeding 30 To 49 over 14-Feb-08 50 Cheque 23232 317.5

14-Feb-08 50 Cheque TD 023 267.5

20-Feb-08 100 Cheque td32 167.5

10-Mar-08 167.5 Cheque td35 0 Ashley Brown Speeding 0 to 15 over 04-Mar-08 100 Cheque A4545 267.5

04-Feb-08 150 Debit CIBC 456 117.5

28-Feb-08 75 Cheque CIBC 78 42.5

Also, There will only be 3 entries for each client.

I need to create a macro that will move information from a range of columns and then insert them under existing information in another column.

here's my example, this is what I'm starting with:

There are only 3 rows in this worksheet and I have numbered the columns just for reference in solving this problem: Anyway, this is how it looks when I import the records:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 First Last Offence Retained 1st. AMT PMT Method Receipt # Bal Fwd Date AMT Pmt Method Receipt # Bal Fwd Date AMT Pmt Method Receipt # Bal Fwd Date AMT Pmt Method Receipt # Bal Fwd Ashley Brown Speeding 0 to 15 over 04-Mar-08 100 Cheque A4545 267.5 #### 150 Debit CIBC 456 118 #### 75 Cheque CIBC 78 43 ######## 43 Cheque CIBC 88 0 Frank Hiller Stunt--Speeding 02-Jan-08 100 Cash cash 267.5 #### 50 Cash cash 218 #### 100 Cash cash ## 02-Apr-08 118 Cash cash 0 jane doe Speeding 30 To 49 over 14-Feb-08 50 Cheque 23232 317.5 #### 50 Cheque TD 023 268 #### 100 Cheque td32 ## ######## 168 Cheque td35 0

And this is how I need it to look after the information is moved and re-inserted:

1 2 3 4 5 6 7 8 First Last Offence Date 1st. AMT PMT Method Receipt # Bal Fwd Frank Hiller Stunt--Speeding 02-Jan-08 100 Cash cash 267.5

19-Feb-08 50 Cash cash 217.5

06-Mar-08 100 Cash cash 117.5

02-Apr-08 117.5 Cash cash 0 jane doe Speeding 30 To 49 over 14-Feb-08 50 Cheque 23232 317.5

14-Feb-08 50 Cheque TD 023 267.5

20-Feb-08 100 Cheque td32 167.5

10-Mar-08 167.5 Cheque td35 0 Ashley Brown Speeding 0 to 15 over 04-Mar-08 100 Cheque A4545 267.5

04-Feb-08 150 Debit CIBC 456 117.5

28-Feb-08 75 Cheque CIBC 78 42.5

Also, There will only be 3 entries for each client.
This is a copy of the macro that I have so far:

Sub MoveRow2Data()
Dim X As Long, Z As Long
Dim LastColumn As Long
Const StartCol As Long = 9
Const GroupCount As Long = 5
Const MoveToColumn As Long = 4
Const DataRow As Long = 2
With Worksheets("TrustDepositCSVfile")
LastColumn = .Cells(DataRow, .Columns.Count).End(xlToLeft).Column
For X = StartCol To LastColumn Step 5
For Z = 0 To GroupCount - 1
.Cells(DataRow, X + Z).Copy _
Destination:=.Cells(X - 9 + DataRow + Z, MoveToColumn)
Next
Next
.Cells(DataRow, StartCol).Resize(1, LastColumn - _
StartCol + 1).ClearContents
End With
End Sub

Can anyone help me create this macro? Thanks, Monaleasa

Hello,
I have two questions where I need some help on.

1. I'm trying to create a macro, assign it to a command button on the source sheet for users to click that button to go directly to another sheet in the same workbook. It seemed easy to write in VB as worksheets ("sheet#").select but I get an error when pressing the command button.

2. I also tried to create a macro on My Personal.xls and for some reason will not allow me to delete it. There are no macros now on the file but the file is somewhere and I would like to just delete it. I found that I can
hide it only....

I'm new to macros and really need some help. I do not know enough and I think I played too much therefore causing errors.

Can I get some help?
L'

Hi,
I'm trying to create a macro that will create a vendorId from the FullCompanyName. I have to copy the 1st 3 characters of the FullCompanyName , paste it into the VendorId and add 000. The result will be for example 1 ; 02W000.

---A-----------B-----------------C-----------------D----------
Vendor ID----CompanyID----Company Type------FullCompanyName
02W000----02Works--------Staffing--------------02Works
Aar000-----Aaron Bullock--- Staffing--------------Aaron Bullock
------------Abbie----------Staffing--------------Abbie Demattei
------------Abeline---------Staffing--------------Abeline Inc.
-------------------------------------------------------------------------------------
I've tried setting up a
Do While
...........
..............
Loop

but I can't figure out how to code a variable associate with the row that will be incremented by 1 to loop through the entire file.

There are thousands of rows, way to much to do manually

Thanks,
Ralph