Windows Support Forum

Solved: Excel -Macro or Formula? Replace my data with similar required data - sample

Q: Solved: Excel -Macro or Formula? Replace my data with similar required data - sample

When i export my inventory from my DB into an excel file my trailer brands are listed as they are in my DB of course But when i bulk upload them with XML feed to trailer inventory sites HTW com amp HTT com for our example here My inventory is listed on their site but it s not searchable because the brand search is a Replace with similar - sample data required Solved: Formula? Excel my data or -Macro drop down Solved: Excel -Macro or Formula? Replace my data with similar required data - sample menu on these sites Say i goto HTW com and do a search for Blue Ribbon trailers none of my blue ribbon trailers show up because all of Solved: Excel -Macro or Formula? Replace my data with similar required data - sample mine are listed as BLUE RIBBON TRAILERS LTD and the site is searching for Blue Ribbon and although they are the same trailer brand HTW com does not see them What i m looking for is maybe a Macro or even formula that once i tell it what site i m working on will go through my list and replace all my spellings with the proper ones for that site Attached is an XLS file with columns the first is my list as is from my DB the other columns are the proper spelling for each corresponding site Excel s find and replace feature is a VERY crude fix to this but since it s so time consuming it s NOT a viable solution Any takers Slurpee Zack Anyone nbsp

Relevancy 100%
Preferred Solution: Solved: Excel -Macro or Formula? Replace my data with similar required data - sample

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/directdownload.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Solved: Excel -Macro or Formula? Replace my data with similar required data - sample

for the record, a complete solution would be GREAT, but not necessary...
I just need someone to point me in the right direction and maybe a small working sample of like 5 brands and i'll expand the list from there...

cheers

https://forums.techguy.org/threads/solved-excel-macro-or-formula-replace-my-data-with-similar-required-data-sample.974275/
Relevancy 95.48%

Hi,

I have several completed templates that I need to summarize into a spreadsheet.

Is there a way for me to list down the files and the location of the excel spreadsheets and run a macro for copying the data I need from the files listed and pasting them into a spreadsheet format?

Here's what I am doing now, manually.

1. Open file1
2. Copy B2
3. Paste to SummaryFile on B1
4. Copy C2
5. Paste to SummaryFile on B2
6. Copy D2
7. Paste to SummaryFile on B3
8. Close file1

9. Open file2
10. Copy B2
11. Paste to SummaryFile on C1
12. Copy C2
13. Paste to SummaryFile on C2
14. Copy D2
15. Paste to SummaryFile on C3
16. Close file 2

I hope somebody can help.

Thanks,
Monica
 

A:Solved: Excel Macro needed for creating a spreadsheet from data on several but similar files.

https://forums.techguy.org/threads/solved-excel-macro-needed-for-creating-a-spreadsheet-from-data-on-several-but-similar-files.632147/
Relevancy 86.8%

Hello I have two problems I have a lot of scanning data all date and time stamped All the scans show different status of shipped packages all shipments have unique ID but there are multiple scans data export from macro to second Solved: excel ACCESS: and Show latest data on route for all Solved: ACCESS: Show second latest data and export data to excel from macro of them to track the packages I want to find all the packages for which there was quot misdirected quot scan then for all of them show the scan before So the end result would be all the scans that show for each package where it was misdirected the terminal of the scan before the misdirect scan I want to automate this run queries every two hours between am and pm copy and paste all results into Excel to specified areas e g first query results to A second to A third to A etc or onto different tabs save as csv to a specific folder then send it as attachment to a given e-mail address Even partial solution can be good I only know macros somewhat not as much VBA so I tried that with the TransferSpreadsheet command but can t get it work Any help is greatly appreciated nbsp

A:Solved: ACCESS: Show second latest data and export data to excel from macro

https://forums.techguy.org/threads/solved-access-show-second-latest-data-and-export-data-to-excel-from-macro.942280/
Relevancy 84.01%

Hiya

With the Lookup Function Sample Data spreadsheet, you can learn how to use Microsoft Excel to use the VLOOKUP function to search through columns of data. This file is intended to be used in conjunction with the article

Two tips for finding records in large spreadsheets.

System Requirements
Supported Operating Systems: Windows 2000, Windows 98, Windows ME, Windows NT, Windows XP
This download works with the following Office applications:
Excel 2002
Excel 2000
http://www.microsoft.com/downloads/...10-31aa-44d6-baaf-c0e5c83f6dd9&DisplayLang=en

Regards

eddie
 

https://forums.techguy.org/threads/excel-2002-sample-file-lookup-function-sample-data-jan-6.111970/
Relevancy 80.91%

I have a spreadsheet that contains formulas. I want a macro that will copy the data only in that cell with the formula and paste only the data in another cell.

I have been scratching my head for a little bit on this one.

Thanks
 

A:Excel - copying data and pasting data in a formula cell

If I understand correctly, you can actually avoid using copy altogether.

Let's say you have the value 50 in A1 and the formula =A1*2 in A2. The following macro would copy A2 to D2:

Sub Macro1()
Selection.Copy
Range("D2").Select
ActiveSheet.Paste
End Sub

but because the formula uses relative references, the resultant formula (in D2) would be =D1*2.

So what you could do instead -- using a single command only -- is:

Range("D2").Value = Range("A2").Value

HTH,
bomb
 

https://forums.techguy.org/threads/excel-copying-data-and-pasting-data-in-a-formula-cell.512540/
Relevancy 105.78%

Hello again,

I have a word doc that has a form in it. The form may have any number of fields (boxes) to type text answers into, or be a drop down list to select an answer from. I need an excel macro that extracts all data from the form, but with every five fields starting a new row in excel. So all data from the form will appear within 5 columns in an excel spreadsheet.

field 1 field 2 field 3 field 4 field 5
field 6 field 7 field 8 field 9 field 10
etc

Is that possible? I have had no luck to do it myself.

Sample form attached, thank you
____________________________
 

Relevancy 105.78%

I set up an Excel Spread Sheet to add one year to a date I enter in previous column. Only problem is that when previous column is empty formula calculates a date (12/31/00) anyway rather than leave the column blank until a date is entered in previous column. I hope I am making myself understood.
gb
 

Relevancy 104.92%

Hi I have been asked to data Excel help Solved: manipulate required to track the usage of certain roles in my team there can be more than of each role hence over and to display a monthly summary of Solved: Excel help required to manipulate data the remaining availability which can be displayed to the customer So far I have I have created a sheet to display the data to show who is doing what in the team and I use this data to form a seperate sheet which provides a summary on a weekly basis of what availibility there is for each role type I hace attached what I have completed so far My next step is to create something which only returns the availability data on a monthly basis for the role types and this is where my skills have run out I have tried a few things with no luck so far Does anyone have any suggestions on what I could do I am a very much a light user of Excel so anything complicated will need some explaination please Thanks in advance Mike nbsp

Relevancy 104.49%

hope I named the thread right Good day everyone This one thing no Excel 2007, entered. formula stop Solved: data if IF in Excel has been driving me crazy for a couple of hours now I ve looked all over the internet Solved: Excel 2007, stop IF formula if no data entered. trying to find the solution and still couldn t Hoping to find the solution here otherwise I might stay up all night tonight trying to make this work so I m putting together this custom spreadsheet for my own little needs let s say I have Solved: Excel 2007, stop IF formula if no data entered. A B and C cells in the C cell I Solved: Excel 2007, stop IF formula if no data entered. want to display quot quot if the numbers in A and B are equal and they are not equal then display nothing so the formula I m using is IF A B quot quot and is there are any numbers in A and B excel interprets everything just fine but if if there are no numbers yet it takes two empty cells as having the similar data and still gives me quot quot in C cell so how do I make so that Excel does not execute the IF formula before I enter the numbers in A and B or is there another of of not getting the quot result quot before actually putting in the numbers hope that someone can help me with this thing nbsp

Relevancy 104.06%

Hiya

System Requirements

Excel 97 or higher

Operating System - Windows 95 & 98, NT 4.0 & 2000, Windows Me, Win XP

http://www.microsoft.com/downloads/release.asp?ReleaseID=43954&area=search&ordinal=9

Regards

eddie
 

https://forums.techguy.org/threads/converting-excel-97-data-to-html-sample-oct-10.99330/
Relevancy 103.2%

Hey,,

Here's the deal

I am linking through excel 2007 to an access 2007 DB Query (with columns A & B).
Column A & B show correctly in excel I have added a third column C, to calculate the running sum of A&B
Thus
C1 = Running Sum
C2 = (A2+B2)
C3 = (A3+B3)+C2
C4 = (A4+B4)+C3
C5 = (A5+B5)+C4
C6 = (A6+B6)+C5
...and so on and so forth...

Now I hit the refresh external data button and ......$%##!!!
Suddenly Excel in all his wisdom decided that he feels the need to edit my formula...
Now it reads as follow:
C1 = Running Sum
C2 = (A2+B2)+C1 //the "+C1" is automatically inserted by Excel & results in a Error
C3 = (A3+B3)+C2
C4 = (A4+B4)+C3
C5 = (A5+B5)+C4
C6 = (A6+B6)+C5
...and so on and so forth...

How do I kill this bug?
 

A:Solved: Excel 2007 - Formula Error After External Data Update

The problem is that when you refersh you must have chosen to clear the table so your formula is lost.
What I usually do is refresh and the then have it run a amcro afterwards that reinserts my column with my needs.
 

https://forums.techguy.org/threads/solved-excel-2007-formula-error-after-external-data-update.926722/
Relevancy 103.2%

I have a list of branches. We have a closed branch that I need to have inserted onto this report. There are times that the closed branch shows up becuase they had warranty work to do, but most times it does not show up. This is branch 046.

How do I write a macro to check for the branch and if not there add it in?

To add more to this - each branch should show up in 2 different rows in the same report. So I really need to do this for both Branch 046 and for Net Sales - Branch 046

Example
Branch 045
Branch 046
Branch 047
Branch 049

Net Sales - Branch 041
Net Sales - Branch 045
Net Sales - Branch 046
Net Sales - Branch 047
 

Relevancy 103.2%

Hi I need a help Macro two for from data excel combining Solved: sheets with a macro I have two separate sheets that I need to combine together Sheet contains Solved: Macro for combining data from two excel sheets combinations of PROCESSES e Solved: Macro for combining data from two excel sheets g - and - columns B and C and sheet two contains additional data PRGRAMS for each of the process each program on separate row - the Solved: Macro for combining data from two excel sheets process number linked to sheet is in column A So for process there are programs rows for process there are rows and for there are rows I would like to create a third sheet that combines PROGRAMS from Sheet based of given combinations of PROCESSES from sheet and attaches LHS for the left side of the rule and RHS for the right side of the rule I attached a shortened example I need this for combinations There could be a way in inserting number of rows into sheet e g for the combination - there would be the number of rows added that equales the count of quot quot count of quot quot programs from Sheet and the programs would be pasted I hope its not too complicated Thanks a lot for your help Marketa nbsp

Relevancy 103.2%

I m in a bit of a pickle I have an excel document with a summary page quot Tracker quot This worksheet has a table in it with a whole heap of customer information and at the end there are columns for revenue summaries Every month I run a report that I add in a new worksheet which is called quot Relevant Month Revenue Report quot and then I do a vlookup to transfer the revenue and product information in columns quot C quot and quot D quot of the report into the relevant month columns in the quot Tracker quot This has been working well for me but i ll be handing this role over soon so I to Solved: worksheets one into Macro data transfer Excel several from was wondering if there was a macro that I could use that would do this vlookup automatically for the relevant month The Solved: Excel Macro to transfer data from several worksheets into one information in the reports is not in any particular order so i can t do a simple copy paste into my quot Tracker quot summary Any help would be greatly appreciated nbsp

Relevancy 103.2%

What would be the best way to find a specific Column Header, and copy the data found in the column until the column header is found again?
 

Relevancy 103.2%

Hi,

I have a worksheet containing various data including an expiry date, this is created by adding 364 to the start date and then then using an auto fill to fill in all other rows (over 50,000).

I would like to make a macro to do this alongside a number of actions but when making the macro I am specifying only a certain range to fill (A1:A50000). The problem I have is this list is consistently growing and so this range would then not include any new records added. Is there a way that I can make it so it auto fills from A1 to the last present row?

Thanks for your help

Dan
 

Relevancy 102.34%

Hi all,

I am trying to develop a cost calculator based on several drop down lists. I have used the following formula:

=AVERAGEIFS('Scope Of Work'!I:I,'Scope Of Work'!C:C,C6,'Scope Of Work'!F:F,'Cost Calculation'!D6,'Scope Of Work'!G:G,'Cost Calculation'!E6)

Cells C6, D6 and E6 in the Cost Calculation Tab are all drop down menus based on data validation. The formula works when all 3 cells have a selection value but I also want it to calculate if only one or two of the drop down lists have been populated, any ideas?

Thanks,

Mark.
 

Relevancy 102.34%

Hi, I need a macro to use in Excel 2003 that will select all data in a column except the header row. At the moment i am using the following command which works well in most cases

Range("A1").Select
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select

The catch to this is that if the column contains only one row of or no data then the command selects the entire column! Eek, can anybody please help me to fix this?
 

A:Solved: Excel macro to select data in a column with a header row

Dim lastRow as long
LastRow = worksheetfuntion.Max(2, Range("A" & rows.count).end(Exlup).Row

The variable will contain the last filled row in column A, the Max function will make sure that it will at least be row 2 just in case the column is empty

Then all you need is Range("A2:A" & lastRow).Select
Or if you need more columns then Range("A2:H" & lastRow).select
 

https://forums.techguy.org/threads/solved-excel-macro-to-select-data-in-a-column-with-a-header-row.1024077/
Relevancy 102.34%

Hi,
I have an employee list excel file (vacation) including the details about empno, emp name, last vacation, next vacation, etc in sheet 1.
Now, I would like to write a macro where I will enter the name of the month in a dailog box and the macro should display the list as in sheet 2 of the attached file.

Windows 7 with MS Excel 7.

Kindly help me, its urgent.
 

A:Solved: Excel Macro to display data based on month

I've made a quick macro that does as you requested. It is not "smart" at all so if you change where the data is located (by adding a column for example) it will no longer work. It will handle more rows of data no problem. See the attached.
 

https://forums.techguy.org/threads/solved-excel-macro-to-display-data-based-on-month.1058458/
Relevancy 102.34%

Hello I m finally getting near to the end of my mission to get my trading website online but I have one nutty task left to crack with my data and I could do with a bit of help please I have about products graphic novels - I run a comic shop and I have painstakingly put together my related product info in a spreadsheet laid out as in the current layout below For simple I data my of to rejig the excel available require an macro Solved: there Is do most of the Solved: Is there an available macro to do the simple rejig of my excel data I require products there are related products but for Solved: Is there an available macro to do the simple rejig of my excel data I require some there are only or The simply pertains to the order from left to right the related products are shown on the site What I need to do is get them into the required layout shown below so I can just cut and paste them into a specific Access table that already exists in the online trading software However I m struggling with this as my Excel skills don t really go in macros and loops and whatnot I m pretty chuffed with myself when I use something like concatenate I get the impression this should be reasonably straightforward though which is very frustrating Can anyone recommend an available macro or bit of code that does this sort of thing Or am I just missing some very obvious functionality in excel many thanks in advance Jonathan CURRENT LAYOUT this is meant to be columns - Product Reference but it doesn t want to tab nicely Product Reference REQUIRED LAYOUT similarly this is meant to be three columns - Product Reference Related Product Order Product Reference Related Product Order nbsp

A:Solved: Is there an available macro to do the simple rejig of my excel data I require

https://forums.techguy.org/threads/solved-is-there-an-available-macro-to-do-the-simple-rejig-of-my-excel-data-i-require.893352/
Relevancy 102.34%

Hi everyone,

Let me just start by saying this forum is awesome. Nothing like getting a new perspective to solving a problem!

Here's the scenario:
I have 3 sheets: Input Sheet, Output Sheet, Translation Table

Both the Input Sheet and Output Sheet has columns of data with a header row in row 1. I need to move entire columns of data from the Input Sheet to the Output Sheet based on the header row.

This is where the Translation Table comes into play. I need to move the columns of data based on what is defined in the Translation Table.

For example, all data in column "Column A" of the Input Sheet should be moved to column "Albert Column" of the Output Sheet.

A sample file is attached to better illustrate.

Thanks in advance for the help!

Simon
 

Relevancy 102.34%

I'm a novice excel VBA macro user. I've manipulated already created macros, but I'm not sure how to create one from scratch. Can someone please help me create a macro.

I'd like to move the values highlighted in yellow to the cells highlighted in green...and then delete any rows that are empty.

Thanks in advance for the help.
 

Relevancy 101.91%

Hiya

Moving Data from a Database into Microsoft Excel 97 Using Fields

System Requirements

Excel 97 or higher

Operating System - Windows 95 & 98, NT 4.0 & 2000, Windows Me, Win XP
http://www.microsoft.com/downloads/release.asp?ReleaseID=43948&area=search&ordinal=14

Regards

eddie
 

https://forums.techguy.org/threads/moving-data-from-a-database-into-microsoft-excel-97-using-fields-sample-oct-10.99325/
Relevancy 101.05%

Hiya

The Microsoft Data Analyzer design and create data cubes article sample files provides sample source data for creating data cubes from text files. Use this file in conjunction with the article Design and Create Data Cubes from Text Files.

http://office.microsoft.com/assistance/2002/articles/daCubePrinciples.aspx

http://office.microsoft.com/downloa...seID=43350&area=search&ordinal=12&redirect=no

Regards

eddie
 

https://forums.techguy.org/threads/data-analyzer-3-5-design-and-create-data-cubes-sample-files.97614/
Relevancy 101.05%

Hoping to get some help on this question please I have reviewed the forum and found a similar post but the sample data is more predictable than mine and the by sort to data then to Macro Excel date, cell Solved: new move Macro posted doesn t work on Solved: Excel Macro to sort data by date, then move to new cell my data See post quot Excel Macro to Move data in Rows to column quot -Aug- AM quot On my data I would like to be able to take the yellow row and move the data up to the previous row starting in column quot R quot The rows I would like on the same line all begin with a date I don t know how to make a looping Macro that will identify the quot date lines quot and take the quot Solved: Excel Macro to sort data by date, then move to new cell second date line quot and move it to the quot first date line s quot row Any help would be appreciated I have attached some sample data I hope that s okay Thank you Christine PS - The range on data varies nbsp

A:Solved: Excel Macro to sort data by date, then move to new cell

Christine,

this macro below works well with the sample data.
Code:
Sub sorting()
Dim Rng As Range, c As Range, i As Long
Set Rng = Range("A:A").SpecialCells(xlCellTypeConstants, xlNumbers)
For Each c In Rng
i = (i + 1) Mod 2
If i = 0 Then
With c.Resize(, 15)
.Copy .Offset(-1, 17)
.ClearContents
End With
End If
Next
End Sub

How it works:
1. Takes all those cells in column A of the active sheet that contain numbers. (Dates are actually numbers, only formatted to look like dates.)
2. In a loop, it takes every second cell of the set of cells selected in Step #1.
3. Takes a 15 cells wide region starting with the cell selected in Step #2, and copies it to the region starting with the cell positioned one row up and 17 columns to the right.
4. Deletes the content of the original 15 cells wide region.

Caution:
1. If there are any numbers in column A besides dates, then the algorithm is wrong and the macro will fail.
2. If the dataset in the second date line is wider than 15 cells wide then the rest will remain in place. But the macro can be easily updated, just change the Resize line.
3. Don't run it twice on the same worksheet.
4. Always make a backup copy of your data before running the macro, because it is not Undo-able.

Jimmy
 

https://forums.techguy.org/threads/solved-excel-macro-to-sort-data-by-date-then-move-to-new-cell.900057/
Relevancy 101.05%

I need to write a macro that Anot Spreadsheet Writing Move Excel for From Help to Data One Macro a Solved: to Need will copy data from one spreadsheet into a separate spreadsheet not Solved: Need Help Writing a Macro for Excel to Move Data From One Spreadsheet to Anot in the same file Here is the scenario On the first spreadsheet I have a customer s name in one column and a customer s address in another column On a separate spreadsheet Solved: Need Help Writing a Macro for Excel to Move Data From One Spreadsheet to Anot a have those same customer s names without the addresses On the second spreadsheet the Solved: Need Help Writing a Macro for Excel to Move Data From One Spreadsheet to Anot customer s name may be listed more than once What I need to do is add the corresponding address to the second spreadsheet beside the name of the customer identical data from both spreadsheets The reason copy and paste won t work is because the customer may appear numerous times on the second spreadsheet and their are approximately rows of data The macro needs to basically say IF Spreadsheet Column A customer equals Spreadsheet Column G customer then ADD REPLACE info from Spreadsheet Column B address to Spreadsheet Column H currently blank I just can t figure out how to make it do that ANY HELP ANYONE CAN GIVE IS APPRECIATED nbsp

A:Solved: Need Help Writing a Macro for Excel to Move Data From One Spreadsheet to Anot

https://forums.techguy.org/threads/solved-need-help-writing-a-macro-for-excel-to-move-data-from-one-spreadsheet-to-anot.732935/
Relevancy 101.05%

I have an issue if certain cells are blank (not sure if they would be null, blank, empty) to have a macro to input "0" in the cell instead. When I look at the format in excel it's under general.

I currently have the following and it puts nothing in the cell. (or seems to be nothing in the cell) I can manually enter a "0"(zero) in the cell and it shows.....

Sub Addifblank49()
Sheets("BPS").Select
Range("c16").Select
Var = Selection.Value
If IsNull(Range("C16").Select) Then Var = "0"
End Sub
I've tried using val instead of var and it won't let me. I'm not real familiar with VBA so any help would be awesome!!
 

Relevancy 100.19%

Hello my fellow databasers The Information I am currently working for a company that is tracking their part s expenses per each customer job All customers expenses are entered into the same primary workbook so entering information is easier than going to each customer workbook The column headings are as follows Customer Name - Date - Part Used - Cost - Invoice - Additional Notes There is primary workbook and then each customer has their own workbook I have researched for a week now and am not savvy enough with Excel to know exactly what I should search for I believe a macro is what I want though The question How or what would I do to have each workbook search for its copy substitute workbooks to or Solved: 2010: Excel Macro automatically between data relative Customer Name and paste auto populate the information into the workbooks ex I entered a part for John Solved: Excel 2010: Macro or substitute to automatically copy data between workbooks Doe within Primary Workbook The second workbook sees John Doe copies the row of all parts that have John Doe in the first column and populates it in the second workbook Also would I need to create a Solved: Excel 2010: Macro or substitute to automatically copy data between workbooks separate Vlookup Solved: Excel 2010: Macro or substitute to automatically copy data between workbooks first to add a value to my customers so it wasn t text based to help the above equation work I know I could copy and paste each one but sometimes we retroactively change values of costs and for the amount of time I could save by creating a macro or some formula would be well worth continued research Any help is appreciated Application It s important for my employer to be able to see what parts were used on each job when settling up with clients It will make his life and mine easier if I can get this function to work Otherwise we are looking through hundreds of lines of parts using the find command Thank you for all help and comments nbsp

A:Solved: Excel 2010: Macro or substitute to automatically copy data between workbooks

hi
A macro can do what you need but have you considered using filters on the primary workbook where you can select the customer name and only those records will be displayed? This will eliminate the need for multiple workbooks and make your life much simpler. An added benefit would be the ability to filter on other criteria like Date Or Part # so you can see across Customers what occurred on a specific date or which customers ordered a specific part.
Alternatively, this would be an ideal application for Access which would make data entry simpler and provide the information you need in the format you need.
 

https://forums.techguy.org/threads/solved-excel-2010-macro-or-substitute-to-automatically-copy-data-between-workbooks.1130580/
Relevancy 99.33%

I have a bunch of rows with certain data. I would like to find data that is similar to this years... For instance, I want to find data on row #65 here (2011) that resembles another year. I have data that goes back to 1950 so I have 62 rows..

So in other words, I want to find a year that had a number of .66 (or close) in September, -1.06 in August, -.47 in July, ect. ect
 

Relevancy 99.33%

Hi I have created an excel document with sheets XP OS excel The first sheet is for entering in the date and recorded values for fuel usage Opening closing added and removed therefore the consumed amount can be calculated Also the operation type is selected At the bottom of the sheet is a comparison table where the estimated averages for the various operations are compared to the Fuel consumed for that day automatically updated consumed and operation type simple cell formula Now for the please!! formula Value Macro replace Solved: help second sheet I want an automatically updated record of fuel usage The dates are on the left hand side Column A and the operation for the various vessels goes across at the top Row So each cell in between the date and operation has a formula that links to the first sheet So for example -sept- the date on the left has to equal the date you entered on sheet If that condition is satisfied then the operation type that you selected in sheet operation type in the comparison table for the estimated avgs If these conditions are satisfied then The cell Solved: Macro help please!! Value replace formula that corresponds to the date and operation attains the consumed fuel value If it doesn t then it gets a value Formula for cells in nd sheet IF AND Sheet I Sheet D A Sheet B Sheet J ie IF AND Selected operation operation on table Date nd sheet date entered in sheet consumed value calculated in the st sheet Now here s the problem if you change the date on the st sheet say -sept- it will correspond to a different cell on the nd sheet right So the value that you had before corresponding to -sept- will go back to when the date is changed and the cell below it will attain that value Same operation but the next day due to the formula it doesn t satisfy both conditions for -sept- yeah So I want the value that was determined by the formula on the nd sheet corresponding to -sept- to stay ie Value overrides formula and then you can determine the -sept- valu without manual input in the nd sheet when the date is changed in st sheet So u now have values without actually touching the second sheet I don t want any to alter the second sheet it s just meant to be for an automatically updated record for fuel usage so we can determine a new average etc So pretty much i need the macro to define the range starting from B and goes across and down many many cells Then within this range the non values should be isolated selected copied paste special values And all other cells ie values should either be skipped or selected copied paste special formulas In the end i would like the first sheet to have a button labelled quot update quot once pressed it will update the nd sheet automatically amp clear the values entered in the st sheet I know it s a lengthy request but i ve been working on it for too long and encountered many problems Please help Much appreciated thanks in advance Dusty nbsp

A:Solved: Macro help please!! Value replace formula

All solved...cheers
 

https://forums.techguy.org/threads/solved-macro-help-please-value-replace-formula.787973/
Relevancy 98.9%

I am trying to hide rows of my worksheet that show a date in the completed column.
The orders that are complete have a date in column F up to this point I highlight them green so I know they are completed but it would be nice if they highlighted green and were hidden when a date was entered into the completed column.
Can someone assist me with creating a macro that will do this?
I am very new to macros so please forgive my lack of knowledge.

I have attached a sample of what I am doing however inf has been changed.

I am using Excel 2007

Thank you,
 

A:Solved: Macro for excel 2007 to hide rows based on any data entered in a column

Hi, welcome to the forum,

I put some simple code in the Sheet's vba

Make sure you allow macro's to be run

Just enter a date in the last row of column F for testing.

For the other green rows, just click in column F and update the date by retyping it
 

https://forums.techguy.org/threads/solved-macro-for-excel-2007-to-hide-rows-based-on-any-data-entered-in-a-column.1076999/
Relevancy 98.04%

Hi,

I want to know how i can export the data from Access to excel using Access VBA for the specified sheet using data linkage with access database. Like we used to do it manually in excel as external data from access.

So that the excel size won't be that big and also it saves processing time.
 

Relevancy 96.32%

Hi,

I was wondering if anyone could help me. I need to extract data from multiple excel files into a new excel file and sum the values. The data comes from excel files that are made from a template so the data is always in the same place or same cells. For example, i have data saved daily in excel files with the following naming convention 080204, 080304, 080404,..... and so on. What i would like to do is have a macro that will create a week ending sheet by importing and summing the data from that week.

Thanks for reading!
 

A:excel macro for copying and pasting data from multiple excel files

thanks for all those who read!!!! let me know if anyone needs the code!
 

https://forums.techguy.org/threads/excel-macro-for-copying-and-pasting-data-from-multiple-excel-files.267990/
Relevancy 95.89%

I m working on a Bill of Material creation automation project that requires some expertise in VBA and I have none The objective is to run a macro from an excel spreadsheet called quot PGE import code to VBA Macro access data to Excel - excel BOM quot to do the following Go to the folder quot C Documents and Settings Desktop Auto Project quot Find all the mdb databases in this folder Find quot HistoricalMaterialItemsAll quot table in EACH of those databases in step and import the data from Excel Macro - VBA code to import access data to excel the columns listed below Excel Macro - VBA code to import access data to excel into PGE BOM xls s columns C through G DrawingNumber ItemNumber Quantity PgeCode Description The following is a VBA code that my friend had written in Excel Unfortunately I have an older version and the code does not seem to be compatible with Excel Sub ImportAccessData Stop dPath quot C Documents and Settings Desktop Auto Project quot sFile quot MDB quot strSrch dPath amp sFile Set TargetWB Application ActiveWorkbook Set TargetWS TargetWB ActiveSheet sRow bFile False If Dir strSrch lt gt quot quot Then strFlNm Dir strSrch bFile True End If Do Until bFile False strPath dPath amp strFlNm Call GetData strPath strFlNm Dir If strFlNm quot quot Then bFile False Loop End Sub Sub GetData fl Stop strSQL quot Select HistoricalMaterialItemsAll From HistoricalMaterialItemsAll quot Workbooks OpenDatabase fl strSQL xlCmdTable Set WB Application ActiveWorkbook Set WS Application ActiveSheet iRow Do Until WS Cells iRow quot quot TargetWS Cells sRow WS Cells iRow Get the Description iRow iRow sRow sRow Loop Application DisplayAlerts False WB Close Application DisplayAlerts True End Sub The quot ImportAcessData quot sub procedure above works just fine However the red statement quot Workbooks OpenDatabase fl strSQL xlCmdTable quot line in the quot GetData quot sub procedure seems to be incompatible with excel Could someone please help debug this Thanks nbsp

A:Excel Macro - VBA code to import access data to excel

Hello, and welcome to the board!

When posting code, please use CODE tags, which extremely helps with readability.

Perhaps you could try using the code constant???...
Code:

Option Explicit

Sub ImportAccessData()
Dim dPath As String, sFile As String, strSrch As String
Dim TargetWB As Workbook, TargetWS As Worksheet
Dim sRow As Long, bFile As Boolean, strFlNm As String, strPath As String
dPath = "C:\Documents and Settings\Desktop\Auto Project\"
sFile = "*.MDB"
strSrch = dPath & sFile
sRow = 2
bFile = False
Set TargetWB = Application.ActiveWorkbook
Set TargetWS = TargetWB.ActiveSheet
If Dir(strSrch) <> "" Then
strFlNm = Dir(strSrch)
bFile = True
End If
Do Until bFile = False
strPath = dPath & strFlNm
Call GetData(strPath, TargetWB, TargetWS)
strFlNm = Dir()
If strFlNm = "" Then bFile = False
Loop
End Sub

Sub GetData(fl As String, WB As Workbook, ws As Worksheet)
Dim strSQL As String, iRow As Long, sRow As Long
strSQL = "SELECT HistoricalMaterialItemsAll.* FROM HistoricalMaterialItemsAll"
Workbooks.OpenDatabase fl, strSQL, 3 'xlCmdTable
iRow = 2
Do Until ws.Cells(iRow, 1) = ""
ws.Cells(sRow, 7) = ws.Cells(iRow, 5) 'Get the Description
iRow = iRow + 1
sRow = sRow + 1
Loop
Application.DisplayAlerts = False
WB.Close
Application.DisplayAlerts = True
End Sub
 

https://forums.techguy.org/threads/excel-macro-vba-code-to-import-access-data-to-excel.842885/
Relevancy 95.89%

Good afternoon.I have searched the internet and Computing.net for an answer to this and cannot locate one. Any help would be really great!I have a spreadsheet with expenses as part of a person's personal finances. The person can enter either a monthly expense or yearly expense for each item. What I want is regardless of which one they choose to enter, the other period will calculate accordingly.For example,...............................A..........................B....................1........................Monthly.................Yearly................2...Rent.................??.........................??...................3...............................................................................If the person enters $800 under monthly I want the Yearly cell to automatically calculate the annual amount. If the person chooses to enter a Yearly amount I want the Monthly cell to automatically calculate the monthly amount.What formulas would I place in the Monthly (A2) and Yearly (B2) cells so this can be done?Thanks again.

http://www.computing.net/answers/office/excel-formula-depending-on-data-entered/20561.html
Relevancy 95.89%

I have a budget workbook set up in Excel, and in that workbook there are many different sheets all part of the one book.

I have created a formula in one sheet from the data that is collected there, and that data is important for information collected on another sheet. I want to copy the formula and the data from that from that sheet and have it appear in the other excel sheet. I also want the data to change on the second excel sheet as the results from the formula change in the first sheet.

When i have tried to just copy and paste the formula from one sheet to the next I just get a ref error.

Many thanks for your help in advance.

Regards

Anthony

A:Want to copy data and formula from one Excel Sheet to another.

This may help
How to Copy Formulas in Excel | eHow.com

http://www.sevenforums.com/microsoft-office/144966-want-copy-data-formula-one-excel-sheet-another.html
Relevancy 95.89%

I have a shared excel spreadsheet that has sheets for each user to input data into on a daily basis I then have a master sheet that I compile all the data onto Usually i formula 2003 Excel copy Data have to copy the data from each user sheet individually and then paste it on the master sheet From there I Excel 2003 Data copy formula filter the data on the master and build reports from that data And there lies the problem it is very time consuming to do that on a bi-weekly basis What i m seeking to do is have a formula to do that for me automatically or some way to compile that data quickly on the seperate sheet All this data is in the same workbook I need to be able to compile all the individual cells in column A of the user sheets and paste them into column A of the master sheet colum b from users to column b of master etc without adding cells together or anything like that Each individual cell in a column from each user sheet needs to be copied and have it s own cell of the same column of the master sheet I have user sheets with two tables of data and two masters for each table of data that needs to be transferred over bi-weekly for reporting Anyone know a formula or process that could do this either automatically or quickly without having to manually copy and paste the data I do not have access to VBA or the ability to install anything since this is a work computer nbsp

Relevancy 95.46%

Hi there ... I want to write a macro that would take the user to the next new unique data in a column. I have a list of account numbers that may be listed 3 times back to back in the column or up to 50 times back to back. I have a particular cell pointed at the top of the column, (ie, "=A1") and I want a macro to take the user to the next new account number in that column, which could be any number of rows down.

Suggestions? The cell I want the reference to change is in sheet1 and the account numbers are in a different sheet, same workbook.

Thanks!
 

Relevancy 95.46%

Excel 2003 SP3
Could anybody suggest a way, using whatever method you prefer to copy existing data to another tab, but have the data go transparent by 50%(or thereabouts, so you can just see it), but keep the headings normal font.
Thanks
 

A:Solved: Excel 2003 help to copy data to new tab and make data transparent

https://forums.techguy.org/threads/solved-excel-2003-help-to-copy-data-to-new-tab-and-make-data-transparent.807436/
Relevancy 95.03%

Hi, I have an excel workbook that has a formula that is no longer working because it is saying it cant find the data source. I have would like to know how I can change the formula to look for a new data source in all workbooks?

http://www.computing.net/answers/windows-10/how-to-update-an-excel-formula-to-accept-new-data-source/751.html
Relevancy 95.03%

Hello I am working on a spreadsheet to help employees quickly calculate tank volumes In short we keep track of tank volume by height in ths of an inch I have different worksheets containing the height and volume data of the tanks The sheets are named by formula cell based on Excel Vlookup data tank number So for example tank is in worksheet Excel Vlookup formula based on cell data quot quot In another reference sheet I plan to hide In B column have the heights in ths of an inch In C column I have the volume associated with that height I do have a Vlookup formula that works right now but I would like to use one master sheet and need to be able to enter different Excel Vlookup formula based on cell data tank numbers in a selected cell and have the formula reference the data sheet based on the tank number we enter Each tanks volumes are different which is why this is needed This is the formula I currently use and it works Just not sure how to alter it so Excel Vlookup formula based on cell data I can have a cells value determine the reference worksheet in the vlookup formula VLOOKUP B B C Id like the value to change based on what tank number we type in which will also be the data sheets name to be referenced Maybe adding a drop down selection would even be a better idea than typing the number in Can this be done Thanks in advance for any info Joe nbsp

A:Excel Vlookup formula based on cell data

Welcome to the forum

Hmmm that's a tricky one. So you want the table array to be dependent on the data you type in? I think you can use the INDIRECT function for that (with the function pointing at the data input cell), but you would need to give defined names to all the relevant ranges. For example, you could name the range '2301'!$B$2:$C$519 to something like Tank_2301.

You could use Data Validation to create a drop down list to select the right tank.

How does that sound?
 

http://www.pcreview.co.uk/threads/excel-vlookup-formula-based-on-cell-data.4070303/
Relevancy 95.03%

Hi, I have an excel workbook that has a formula that is no longer working because it is saying it cant find the data source. I have would like to know how I can change the formula to look for a new data source in all workbooks?

http://www.computing.net/answers/office/how-to-update-an-excel-formula-to-accept-new-data-source/20556.html
Relevancy 94.6%

Hello - I want a macro in excel or workspace pro or Happy Harvester web macro Excel geting data that collects info from the web sub page one case after another case till done It should put selected info in excel each case on a different line However the excel quot repeat quot under Excel macro geting web data quot edit quot that I am using fails to accomplish moving to the next line instead it repeats the first line I have made the macro with mouse clicks and dont understand VBA Desired excel result quot ADDRESS CITY OWNER LENDER CASE DATE Maple Xenia Ohio Able Jones Bell Mortgage cv bell st Dayton Ohio Blule Bank America dv Web page listing cases URL http www co greene oh us pa pa urd PAMW Able jones cv brown cv blule cv Sub page with details on one case URL http www co greene oh us pa pa urd pamw o case sum Bell Mortgage vs Able JOnes cv address Maple Xenia Ohio filed status open Thanks thanks so much nbsp

A:Excel macro geting web data

What program did you actually create the macro in? Can you post your existing code?

Rollin
 

https://forums.techguy.org/threads/excel-macro-geting-web-data.971365/
Relevancy 94.6%

I would like to write an Excel macro that includes pausing for data entry from the keyboard. For instance I have a spreadsheet for a simple bookkeeping project. I would like a macro that, when invoked, would do this:
Type "Current income" in the cell the cursor is on.
Move two columns to the right.
Stop for me to (1) enter a dollar amount and (2) hit the "enter" key.
Move two columns to the right.
Stop for me to (1) enter a dollar amount and (2) hit the "enter" key.
End of macro.

My thanks for any help!!
 

Relevancy 93.74%

I need a spreadsheet that will match data in column A. Any time a specific work order number is entered/repeted in that column, and the numbers are entered under each shift, the totals will automatically subtract from the "orders to complete" . It will be a running total subtracting from the Orders to Complete column.wk order # Orders to Complete shift 1 shift 2 shift 3 shift 4 tot orders completed1234 200 10 10 20 30 70456 50 2 2 2 2 8987 500 10 0 0 0 10 1234 0 0 10 50 60987 20 20 20 20 80456 5 5 5 5 20

A:How to Match data, create formula&calculate if data matches

You didn't include any Column Letters or Row Numbers, so I am going to assume that your data looks like this:
A B C D E F
1 wk order # # of wk ordrs shift1 shift2 shift3 tot ordr cmplt
2 1234 200 10 10 20 40
3 456 100 2 2 2 6
4 987 550 20 50 41 111
5 456 20 10 40 70
6 987 10 10 10 30
7 1234 20 5 40 65Based on that table, enter this formula in G2 and drag it down:=B2-SUMIF($A$2:$A$7,A2,$F$2:$F$7)which should give you this:
A B C D E F G
1 wk order # # of wk ordrs shift1 shift2 shift3 tot ordr cmplt orders rem
2 1234 200 10 10 20 40 95
3 456 100 2 2 2 6 24
4 987 550 20 50 41 111 409
5 456 20 10 40 70
6 987 10 10 10 30
7 1234 20 5 40 65Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.message edited by DerbyDad03

http://www.computing.net/answers/office/how-to-match-data-create-formulacalculate-if-data-matches/19857.html
Relevancy 93.74%

Program Steps

The program needs to find words in {} in Template sheet
find relevant column in Data sheet
substitute the data inside {} from the respective column and remove {}
save for all combinations below template

This is something similar to mail merge in word.

Thanks a lot in advance.
 

https://forums.techguy.org/threads/program-to-find-replace-in-excel-data-merge.994574/
Relevancy 93.74%

I have a need to move data from one worksheet that has an associated date to the correct spot in another worksheet with column that runs from 1/1 to 12/21.
Worksheet 2
1/1
1/2
1/3
1/4
...
12/31

Worksheet 1 has:
1/29 45
1/30 23
2/4 13
The macro would read the date in column A of worksheet 1 and put the data into the correct row of column B in worksheet 2. So that after running the macro worksheet 2 would look like this:

1/1
...
1/28
1/29 45
1/30 23
2/1
2/2
2/3
2/4 13
...
12/31

I hope my intention is clear and understandable.

Thanks in advance for any help,
tom
 

Relevancy 93.74%

I have a column that will have values changing but not in consecutive order. For example if I had range A1:A10, only A1, A4, and A9 might have values, but the rest of the cells would be blank. I need to be able to generate A1, A4, and A9 into the next column consecutively as B1, B2, and B3. The A column values could change (including the number of values) and I need the B column to react accordingly. So the next time I run the data, I might have A2, A3, A4, A6, and A10 with data in them - so I need column B to be able to react and put the values (in order) as B1, B2, B3, B4, and B5. Oh and I need all this done in a MsgBox. Can you help? Thanks so much.
 

Relevancy 93.74%

I need assistance with creating a macro. I need to create a lot of different reports but I want the macro to create them for me. Is there way to create a macro by: having a list of data, sorting it all by column A, then copying whatever is the same name in column A (and whatever is associate with column A- to the right) and inserting it into a new worksheet and then doing it for all the different data that is in column A. Then once it is put into the worksheet, to save it with whatever the name is from Column A in the first worksheet.

I figured that there needs to be a loop statement in the end so it will get all the grouped data together into seperate worksheets.

Thanks for your help. I have attached a spreahsheet to get a better idea of what I was looking for.
 

Relevancy 93.74%

Hey Posting here before I received several prompt replies Due to my success I will try once again but this time with a different issue question I have a spreadsheet with column and rows In each row there is a cell block with a series of numbers characters I need some type of formatting macro that will extract only certain characters from each row and place them somewhere else In example I have the following rows r i i o --- u i amp amp amp I need to extra the amp th character of each row whether it be a letter number or space it doesn t matter Now the and th character need to be placed in there own newcolumn to the right of where they were prior So the results would look like this r i i o --- i u i amp amp amp The post here will not let me space it correctly but to the right of the first Creation Formatting/Macro Excel, Data for entry would be quot i quot the second quot quot and the third quot quot So it seems pretty simple but I am not knowledgable enough with formulas macros or vb to be able to figure this out I also need to be able to adjust the formatting macro vb Excel, Formatting/Macro Creation for Data script every couple months as the quot character extraction quot place could change Thank you Excel, Formatting/Macro Creation for Data hopefully someone is familiar with what I am trying to Excel, Formatting/Macro Creation for Data do and can understand this basic issue question Thank you again cg nbsp

A:Excel, Formatting/Macro Creation for Data

Are you okay with just a formula? Try this if your data is in A1:

=Mid(A1,12,3)

12 is the starting position, and 3 is the number of characters to return.

Let us know if you do need it in a macro.

HTH,
 

https://forums.techguy.org/threads/excel-formatting-macro-creation-for-data.447870/
Relevancy 93.74%

Hi I have a major problem crunching large amounts of numbers coming off an instrument I work with but the file format it puts the results into is terrible and I cannot change it So I must export the results in a format which needs some serious formating before it becomes of any use to me I have used excel macros to delete rows and rows with specific text values But now I would like to create a macro that will reorganise this data in a more user friendly format I did use the sort asending command to partially solve the formating problem but it would still require alot of work time to organise it into the final format I need This is the current format of the instrument date Analyte Meas Intensity Rh Y Cs Co Analyte Meas Intensity Rh Y Cs Co Analyte Meas Intensity Rh Y Cs Co Analyte Meas Intensity Rh Y Cs Co Analyte Meas Intensity Rh Y Cs Co Used the quot sort asending quot command to separate data into useful form but need an excel data? create reorganise macro to to get the data into either of the two tables shown below Analyte Meas Intensity Analyte create an excel macro to reorganise data? Meas Intensity Analyte Meas Intensity Analyte Meas Intensity Analyte Meas Intensity Co Co create an excel macro to reorganise data? Co Co Co Cs Cs Cs Cs Cs Rh Rh Rh Rh Rh Y Y Y Y Y I would like to turn the data into this final format using a macro Co Cs Rh Y Can somebody help me please as I have thousands of numbers to crunch ASAP Bye Frank nbsp

A:create an excel macro to reorganise data?

ZIP and post a sample workbook.
Rollin
 

https://forums.techguy.org/threads/create-an-excel-macro-to-reorganise-data.327551/
Relevancy 93.74%

Hi,
I am trying to run a macro which will insert a row of formula to the last row of my data. Say for example.

A1: Formula
A2: 1
A3: 2
A4: 3

I wish to set the macro to always insert into the last row of column A, instead of at Cell A4. Something like,

A1: Formula
A2: 1
A3: 2
A4: 3
A5: Formula

something to do with lastrow formula? Any help would be greatly appreciated.

my current macro is written like this,
'
Rows("13:13").Select <- the formula row
Selection.Copy <- copying the formula
Rows("17:17").Select <- i wish this to go to the last row instead**
Selection.Insert Shift:=xlDown
End Sub

Any help would be much appreciated.

Thanks
 

A:Excel Macro : inserting copied data to the last row

Howdy, and welcome to the board.

Something like this:
Code:

Sub test()
Dim lngLastRow As Long
lngLastRow = Cells(Rows.Count, 1).End(xlUp).Row
MsgBox lngLastRow
End Sub

then you could use for the next formula row:

Cells(lngLastRow + 1,1)
 

https://forums.techguy.org/threads/excel-macro-inserting-copied-data-to-the-last-row.481020/
Relevancy 92.45%

Hi guys i'm hoping you can help me i'm a complete noob when it comes to macro's
 

Relevancy 92.45%

If you check the file attached.

please can you help me in moving data with duplicate names to have the 2nd and 3rd email address moved to a new column and delete the row

need an excel macro to automate this as i am working with a large number of records like this in one excel sheet. i.e, i want to select the sheet and move data in every 2nd row to the columns in row above at the end of columns in that row.
 

A:Excel Macro to Move data in Rows to column

I'm unable to understand the entire requirement, but for moving the records with duplicate names, I've written few lines. This will cut the duplicate record rows (Consider "Pupil Forename" for removing)from the "Report Data" and will post into "Sheet3". Before running this macro please insert "Sheet3". Let me know your requirement, hope I will be able to resolve it out. Thanks!

Code:
Sub test()
Sheets("Report Data").Select
Dim trow As Long
trow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
For i = trow To 1 Step -1
If i = 1 Then
Exit Sub
End If
If Cells(i, 1).Value = Cells(i - 1, 1) Then
Rows(i).Select
Selection.Cut Destination:=Sheets("Sheet3").Range("A65536").End(xlUp)(2)
End If
Next i
End Sub
 

https://forums.techguy.org/threads/excel-macro-to-move-data-in-rows-to-column.1042170/
Relevancy 92.45%

I'd like to create a macro which creates a new MS Word document in the same directory as the excel book which copies specific data from an excel worksheet into the new document. The items contained within the cells should appear as a bulleted list on the word document, not a table.

Is this possible?
 

A:Macro to Export Excel Data to New Word Documet

I'm not a Word macro person at all..But I'm thinking (quick and dirty) you could do this with mail merge. Don't use the address part just 'insert merged' fields...Your 'cells' would have to be in the same row in excel.

There are also excel to word conversion programs(google). I have never used one. If your data was in Access a 'Report' would suit your needs. Also a script that reads the excel file and writes to file and saves as .doc might do this also.

Going to word 'for the sake of being in Word' seems counter productive but management wants what management wants.
 

https://forums.techguy.org/threads/macro-to-export-excel-data-to-new-word-documet.992495/
Relevancy 92.45%

I have a excel file like below.

Column1 |Column2|Column3|Column4|Column5|Column6|Column7
Row 1 EEEE 1 2 3
Row 2 4 5 6

I want to move data in row 2 (4,5,6) to Row 1 columns 5,6,7.

Could you help me with an excel macro to automate this as i am working with a large number of records like this in one excel sheet. i.e, i want to select the sheet and move data in every 2nd row to the columns in row above at the end of columns in that row.

your assistance is greatly appreciated.

thank you
 

Relevancy 92.45%

I'm manually entering payroll data and I feel certain this process could be automated.In one spreadsheet I have the list of employee names and IDs. In another sheet I have the payroll schedule. Each employee ID needs to be copied next to the 21 pay dates in the calendar year. That process repeats for every employee. I understand how to record a macro (what to click) but don't know how to identify mistakes when my recording doesn't do what I intended.How do I create a macro that copies information from one cell in spreadsheet one 21 times down a column in spreadhseet two before moving onto the next cell in spreadsheet one?

A:How to write an excel macro that copies data between sheets

re: I understand how to record a macro (what to click) but don't know how to identify mistakes when my recording doesn't do what I intended."As you probably realize, the macro recorder creates very specific code which will basically do nothing more than repeat the steps you took while creating it. To make matters worse, the code is often bloated and inefficient. It just about always need to be cleaned and modified.One place to start is by reviewing the steps detailed in this tutorial:http://www.computing.net/howtos/sho...These debugging techniques will not only help you "identify mistakes" but it can also help you learn how to write VBA code. By using the debugging techniques discussed, you can reverse engineer code that you find in forums such as these as means to understand how the code does what it does.re: "How do I create a macro that copies information from one cell in spreadsheet one 21 times down a column in spreadhseet two before moving onto the next cell in spreadsheet one?"Without the specifics related to your spreadsheet layout, the best we can offer is a generic solution. Perhaps the following code will help you get started, especially if you use the debugging techniques from the tutorial to help you follow along as the code does it's thing.Let's say you start with this in Sheet1:
A
1 Tom
2 Bob
3 SueThis code will place Tom in A1:A21 of Sheet2, then place Bob in A22:A42, etc.
Sub Copy21Times()
'Determine last Row with data in Sheet 1 Column A
lastRw = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
'Intialize Sheet 2 row counter
rw2 = 1
'Loop through Sheet1!A1:A(lastRw)
For rw1 = 1 To lastRw
'Loop to copy Sheet1 data 21 times
For nxtRw = rw2 To rw2 + 20
Sheets(2).Range("A" & nxtRw) = Sheets(1).Range("A" & rw1)
Next
'Increment Sheet 2 row counter
rw2 = rw2 + 21
Next
End SubYou will note my extensive use of comments. That is a great habit to get into. I didn't include the comments just to help you understand the code, I included them so that the next time *I* look at the code, I'll know what I was trying to do.I have written hundreds of macros for myself and for other people. Without the extensive use of comments, I'd never be able to remember what all of the macros are doing. A very wise programmer once said:"Code Tells You How, Comments Tell You Why"==============================

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

http://www.computing.net/answers/office/how-to-write-an-excel-macro-that-copies-data-between-sheets/19966.html
Relevancy 92.45%

Hi,

I'm looking to set up a macro to do the following:

Open up a workbook called Works Order Register
Find my Works Order on the register by matching the WOnumber (I8) and Contractor name (C1735) from Works Order workbook.
Once found copy various data e.g cell I1735, I1737 etc across to columns D, E etc on the register against the corresponding row found in the previous step.

I've got parts working ok, but not the whole thing together.

Thanks for help.
 

Relevancy 92.45%

Good Morning guys This is my first post on Tech Support Forums I am not to sure how much help I can be to people here other than building gaming Rigs so I will keep one eye on that forum to see if I can add value here Now on to my question I have assembled an Excel spreadsheet with hundreds of lines of date Business Name word Excel individual Need to Macro Maybe?? data documents, Help: Address Contact Number Email Website We have a CRM system which does not allow me to enter new clients myself so i have to complete a Need Help: Excel data to individual word documents, Macro Maybe?? CRM Request form for each potential client on this list over The CRM request form is a Need Help: Excel data to individual word documents, Macro Maybe?? Word Document with sections for each part of the data on excell The problem is I am too lazy to copy and past all of the lines of data into individual CRM Request Forms Need Help: Excel data to individual word documents, Macro Maybe?? Word Documents So in short correct me if I am wrong I think I need to create some form of macro to do this for me copy data from excell open up the word document paste the data in the specific places then save it then repeat for the next line of data It would be greatly appreciated if you could point me in the right direction on this one people Kind Regards Anthony nbsp

A:Need Help: Excel data to individual word documents, Macro Maybe??

https://forums.techguy.org/threads/need-help-excel-data-to-individual-word-documents-macro-maybe.1031904/
Relevancy 92.45%

I have an entire column of data that I need in one cell. When combined I need each piece of data to be surrounded by " " and a , between each one.

151
183
2001

results = "151","183","2001"
 

A:Solved: Excel Column of data place in one cell with "data", "data" results

ASAP's "Merge row data" ("Columns and rows" category) will do 99% of the work for you.

http://www.asap-utilities.com/

(edit: "entire column" -- do you mean full from top to bottom? )
 

https://forums.techguy.org/threads/solved-excel-column-of-data-place-in-one-cell-with-data-data-results.975601/
Relevancy 92.02%

HELP PLEASE !!!!

I have attached a sample excel document
Basically each morning I receive a .txt file and I export it to excel.
I have a macro which deletes blank rows and therefore tidies the file up somewhat as the size is humungous!
There will be approximately 1000 brach i.d.'s on a daily basis within my file, but I have provided a few in the sample excel doc.
Within my attachment I have highlighted a specific branch and the information that comes with it. I only need part of the information (therefore many rows can be deleted within each branch i.d.

Please review the attached sample and keep in mind the following:

1. I need to keep the branch i.d. at the top of each section
2. I really only require the information that relates to "outstanding" and the few subsequent rows beneath

Can someone please assist??? I would be very appreciative
 

A:Need Help with wriitng Macro/code to rid unwanted data and retain specific data

I think this macro will work for you, though with dates involved, you never know.

BTW, I suggest that next time, Excel related threads should be started in the Business Applications section. You get help there sooner. Maybe better help as well.

Jimmy
 

https://forums.techguy.org/threads/need-help-with-wriitng-macro-code-to-rid-unwanted-data-and-retain-specific-data.710324/
Relevancy 91.59%

I need an Excel macro to print a set number of rows from a spreadsheet based on user input. I have attached a sample input file. Each ten rows represents one label. For each 10 rows of data, I want the macro to prompt the user and ask: "How many labels for 5/4X6X12' GROOVED (the description field for each set of data) do you need? So, even though this item shows a quantity of 16, the user may only need to print 2 sets of this data. If the user answers "2", then I need to print 2 pages each having the 10 rows of data that goes with that description:

Type: Delivery
PO #: 38505 SO #: 121046
Ship to: WORK SITE - LOT 3
Address: LOT 3 DALE SUBD
Item: ZZAT0000030621
Desc: 5/4X6X12' GROOVED
QTY: 16 EA
Created by: Rsmith
Ship Date: 11-SEP-14

Any help on this macro would be greatly appreciated. Thank you.
 

https://forums.techguy.org/threads/excel-macro-to-print-multiple-pages-of-order-data.1133764/
Relevancy 91.59%

i was hoping if anyone can provide me code for the macro i need . in my case i have to select the data manually from the source sheet. the selected data range needs to be copied from the active worksheet to the destination workbook. the data should go into specific columns in the worksheet of destination workbook. I have very limited knowledge in coding. If it can be done it would reduce the data entry time drastically.

A:Excel Macro to copy user selected data to new workbook

I assume it can be done, but not without a lot more detail.VBA code needs to know where the data is coming from and where it is going. If the user selects a range, that makes it easier, but the code would still need to know exactly where the data is going. Workbook name, Sheet name/number, the "specific columns" that you mentioned, etc.If you can supply that detail, we can see what we can do for you. If you are going to post any example data, please click on the following line and read the instructions on how to post data in this forum.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

http://www.computing.net/answers/office/excel-macro-to-copy-user-selected-data-to-new-workbook/20228.html
Relevancy 91.59%

In Excel 2007, I have a spreadsheet with 14 columns. Twelve of the columns contain categories of data that correspond to the entries in the other two columns. I need to change the list so that each item/amount in each category occupies its own row, as follows:

Eg.

From this:

col1-----col2-----category1-----category2-----category3-----etc... up to 12 categories
aaa------bbb---------------------$55
ccc------ddd------$44--------------------------$66
To this:

col1------col2------category------amount
aaa-------bbb------2-----------------$55
ccc-------ddd------1-----------------$44
ccc-------ddd------3-----------------$66
I know how to do this manually, but it needs to be done frequently on many different sheets. So, I need a way to do it automatically.

Many thanks for your help
jeannie
 

A:Excel macro - change column data to multiple rows

I have your data mapping from A:N to P:S, with no headers. In case that's wrong, make sure your work's saved before trying this.

Sub test()
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LastRow

For Each Cell In Range("C" & i & ":N" & i)
If Cell <> "" Then
DestRow = WorksheetFunction.CountA(Columns(16)) + 1
Cells(DestRow, 16).Resize(, 2).Value = Cells(i, 1).Resize(, 2).Value
Cells(DestRow, 18) = Cell.Column - 2
Cells(DestRow, 19) = Cell.Value
End If
Next Cell

Next i
End Sub

HTH (welcome to the board)
 

https://forums.techguy.org/threads/excel-macro-change-column-data-to-multiple-rows.992235/
Relevancy 91.59%

Hey everyone I was wondering if anybody on these forums could help me with the below task I run a backup program that generates email reports to a subfolder in my Outlook every - hours to tell me the status of every backup job that I have set up and have running The email details the job name the job start time and the job status running completed successfully failed missed cancelled etc At the moment I sift through each and every one of these emails I have over backup jobs per day and enter the relevant data manually Excel Outlook to data sheet into Run selected on pull macro into my excel spreadsheet The spreadsheet looks like the following Job name Monday st Oct Tuesday nd Oct Wednesday rd Oct Thursday th Oct Job xxx SUCCESS SUCCESS FAILED FAILED Job Run macro on Outlook to pull selected data into Excel sheet yyy SUCCESS SUCCESS SUCCESS RUNNING Job zzz MISSED MISSED CANCELLED SUCCESS What I amp d like is to automate this process so that each day perhaps every hours a script would run that would search for certain values within each email I receive and update the excel sheet accordingly Example - Monday st Oct am amp email comes into inbox with subject body containing amp Job xxx has started amp gt gt gt gt update Excel sheet to record RUNNING under column amp Monday amp in row titled job xxx - Monday st Oct pm amp email comes into inbox with subject body containing amp job xxx completed successfully amp gt gt gt gt update Excel sheet to record SUCCESS still under column amp Monday amp row titled job xxx - As soon as it turns Tuesday whatever emails come into the inbox relating to the specific job the status is recorded in the relevant cell under the column amp Tuesday amp The job names do NOT change amp i e I will always have jobs with the same name running each and every day The emails I receive Run macro on Outlook to pull selected data into Excel sheet contain the following - Subject gt contains the job name job xxx job yyy job zzz - Body gt contains the job status text within the body contains values such as amp the job completed successfully amp or amp the job failed amp - Body gt contains job details amp i e if a job fails it gives an error code I amp d like to include these within the cell when the job fails amp e g amp Failed with error code amp Also do the emails need to be post-processed i e deleted or moved to a different folder once the script has run in order to differentiate between emails containing a new job status and older emails i e it won amp t run the email at am saying amp job started amp again if a more recent email with the same job name has come in with amp job completed successfully amp Now if this script was to run every day of the year it amp d run up a lot of columns within the sheet so is there a way to archive columns For example each month the populated columns for the month from Oct st to Oct st can be moved to another sheet with name amp backupsjobs october amp I amp d really appreciate any help I can get with this If you need any more info please let me know Thanks ever so much for your help Joe nbsp

https://forums.techguy.org/threads/run-macro-on-outlook-to-pull-selected-data-into-excel-sheet.1075419/
Relevancy 91.59%

It s been a while since I have done this and I can kind of picture in Oh - Excel data extracting worksheet pelase Macro into Gurus with help new my head what I need I have surveys source that I need to extract certain data points and copy paste them into another spreadsheet I can get to the point of opening each survey with an input box and copying the cells in the source but it is the paste that is failing me the way I have it set up right now the next survey I open will overwrite the previous survey - I need to make this dynamic so that the next survey I open it will go to the summary file locate the next empty Row and start importing data Moving cell to cell - to the right until complete Please help See Code Dim xl As Excel Application Dim wb As Excel Workbook Dim wb As Excel Workbook Dim ws As Excel Worksheet Dim ws As Excel Worksheet Dim sSurveyPath As Oh Excel Macro Gurus - pelase help with extracting data into new worksheet String Dim sMainPath As String Dim iSheet As Integer input box Dim sMyInput As String sMyInput InputBox quot Enter the Store Number quot quot Survey Store Number quot quot Enter Store Number HERE quot If sMyInput quot Enter Store Number HERE quot Or sMyInput quot quot Then Exit Sub Oh Excel Macro Gurus - pelase help with extracting data into new worksheet End If Summary Set wb ThisWorkbook Loop through Each Sheet in this workbook For iSheet To Select Case iSheet Case sSheet quot Vendor Info quot Case sSheet quot Newspaper Info quot Case sSheet quot Rack Info quot End Select source sSurveyPath quot Oh Excel Macro Gurus - pelase help with extracting data into new worksheet E Myfiles Client Surveys Survey quot amp sMyInput amp quot xls quot sMainPath quot E Myfiles Client Client Surveys xls quot Set xl Excel Application Set wb xl Workbooks Open sSurveyPath xl Visible True xl DisplayAlerts False Set ws wb Worksheets quot Sheet quot Finds next empty row ws Range quot A quot End xlDown Select vendor wb Worksheets quot Sheet quot Range quot C quot Copy Destination wb Worksheets sSheet Range quot A quot wb Worksheets quot Sheet quot Range quot C quot Copy Destination wb Worksheets sSheet Range quot B quot wb Worksheets quot Sheet quot Range quot C quot Copy Destination wb Worksheets sSheet Range quot C quot wb Worksheets quot Sheet quot Range quot H quot Copy Destination wb Worksheets sSheet Range quot D quot wb Worksheets quot Sheet quot Range quot H quot Copy Destination wb Worksheets sSheet Range quot E quot wb Worksheets quot Sheet quot Range quot H quot Copy Destination wb Worksheets sSheet Range quot F quot wb Worksheets quot Sheet quot Range quot C quot Copy Destination wb Worksheets sSheet Range quot G quot wb Worksheets quot Sheet quot Range quot C quot Copy Destination wb Worksheets sSheet Range quot H quot wb Worksheets quot Sheet quot Range quot H quot Copy Destination wb Worksheets sSheet Range quot I quot wb Worksheets quot Sheet quot Range quot H quot Copy Destination wb Worksheets sSheet Range quot J quot vendor wb Worksheets quot Sheet quot Range quot C quot Copy Destination wb Worksheets sSheet Range quot A quot wb Worksheets quot Sheet quot Range quot C quot Copy Destination wb Worksheets sSheet Range quot B quot wb Worksheets quot Sheet quot Range quot C quot Copy Destination wb Worksheets sSheet Range quot C quot wb Worksheets quot Sheet quot Range quot H quot Copy Destination wb Worksheets sSheet Range quot D quot wb Worksheets quot Sheet quot Range quot H quot Copy Destination wb Worksheets sSheet Range quot E quot wb Worksheets quot Sheet quot Range quot H quot Copy Destination wb Worksheets sSheet Range quot F quot wb Worksheets quot Sheet quot Range quot C quot Copy Destination wb Worksheets sSheet Range quot G quot wb Worksheets quot Sheet quot Range quot C quot Copy Destination wb Worksheets sSheet Range quot H quot wb Worksheets quot Sheet quot Range quot H quot Copy Destination wb Worksheets sSheet Range quot I quot wb Worksheets quot Sheet quot Range quot H quot Copy Destinatio... Read more

A:Oh Excel Macro Gurus - pelase help with extracting data into new worksheet

Are you talking about Copy Destination:=wb.Worksheets(sSheet).Range("A3")
and then B3, C3, etc. ??? Is this what you need to be made dynamic using the next available row?

Regards,
Rollin
 

https://forums.techguy.org/threads/oh-excel-macro-gurus-pelase-help-with-extracting-data-into-new-worksheet.874755/
Relevancy 91.59%

i have lost my tally recovery data wheni pasted from pendrive tally data to computer tally.it was replaced

A:tally recovery data how i recover my replace data from my pc

Recover your data in Tally - Some Tips & Tricks to Recover Tally Datahttp://www.apnitally.com/2006/09/re...

http://www.computing.net/answers/windows-8/tally-recovery-data-how-i-recover-my-replace-data-from-my-pc/2147.html
Relevancy 91.16%

Well...I've been reading some of the posts regarding Excel, and it look like Zach is the man!...the expert! Not to say anything less about anyone else.

Anyhow, I'm getting better at my formulas and macors, but I need some help. I need a formula that calculates the anniversary of employee's start date. I would then like the conditional formatting to change the colour to yellow if it's 11 months after their start date...then red if it's on or after their anniversary date.

But wait...there's more...

I would also like Excel to automatically send a reminder email to me when the first condition (yellow) is met.

I've attached the worksheet (not very complex).

Any help would be appreciated.

Thanks!

TBaker14

 

Relevancy 91.16%

I am trying to find a way to insert a formula to the last row with data using Excel coding.

Here is my layout. I only have three columns filled with data (Columns A-C). Columns A & B are not locked, but Column C is locked.

Here is what I would like to happen. I would like to create some code that after data has been entered into Column B, the worksheet is unprotected, a formula is inserted into Column C, and then the worksheet is protected once again.

Does anyoen know if this can be done?
 

Relevancy 91.16%

Hi there,

Could anyone help me with a formula or macro that will read the data in column A and then display a value in column C in the attached .xls?

EZ-Links would display 5
Hutton would display 3

Many thanks!
 

A:Solved: Excel Formula or Macro help please

Place the following formula in C11 and copy down for all rows. Will there ever be any other values that you need to check for?

=IF(A11="EZ-Links",5,IF(A11="Hutton",3,""))

Regards,
Rollin
 

https://forums.techguy.org/threads/solved-excel-formula-or-macro-help-please.831385/
Relevancy 90.73%

Although decently experience with excel, I am quite new with VBA and I cannot "record" this macro, so any help writing this in VBA is much appreciated.

1) I need to create a macro that will take a number that is entered into a cell, and upon hitting a button in the excel sheet create an email where the subject line is the number that was in that original cell.

1b)I would like to do this without changing the name of the excel file though, or at least have another macro that resets the file to its original name.

2) Also, if possible, the same macro/vba as above, only with two cells, a number in one and a name in the other, which then shows up as "Number-Name" as the subject line in the email.

Thanks to anyone that can help - Mike
 

Relevancy 90.3%

Request Excel help either with formula or macro Data will be like the following running into few hundred lines Title Non-Steroidal Anti-Inflammatory Drugs and Antibiotics Prescription Trends at a Central West Bank Hospital Author s Tayem YI Qubaja MM Shraim RK Taha OB Abu Shkheidem IA Ibrahim MA Source Sultan Qaboos Univ Med J Nov - Epub Nov PMID Patent Nil PubMed - as supplied by publisher Other information Title Influence of malnutrition on the disposition of formula help Solved: or either macro Request Excel with metronidazole in rats Author s Jung D Shah A Pharm Res Dec - doi A PMID PubMed - in process Other information Solved: Request Excel help either with formula or macro Title Comparison of Helicobacter pylori eradication rates of standard -day quadruple treatment and novel modified -day -day and -day sequential treatments Author s Sapmaz F Kalkan IH G liter S Atasoy P Source Eur J Intern Med Nov doiii S - - j ejim Epub ahead of print PMID PubMed - as supplied by publisher Patent Other information I want only Solved: Request Excel help either with formula or macro lines begins with Title Author s Source Patent criteria Lines begin with any other words are not required When used with autofilter I have only options to choose To get the required result now I am using autofilter twice first itmes Solved: Request Excel help either with formula or macro begin with and giving it a color next repeating the same with other criteria begining with and giving the result with same color in the third step I am choosing sort be color Request to make the entire process simplified version Excel Thanks in advance nbsp

A:Solved: Request Excel help either with formula or macro

Hi
This macro will extract the target rows into a new sheet called "Results". It also assumes that your original sheet is the First tab in your list of tabs.

Code:

Sub extract()
Dim i As Long
Dim lRow As Long
Dim ShtExsts As Boolean
Dim Count As Long

'Check to see if Sheet "Results" exists, if not create it if yes clear all data
For i = 1 To Sheets.Count

If Sheets(i).Name = "Results" Then
ShtExsts = True
Exit For
End If
Next

If ShtExsts = False Then
Sheets.Add after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "Results"
Else
lRow = Sheets("Results").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("Results").Select
Range(Cells(1, 1), Cells(lRow, 1)).ClearContents
End If

lRow = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
Count = 0

'Extract rows with "Title", "Author", "Source" or "Patent"
For i = 1 To lRow
If Sheets(1).Cells(i, 1) Like "Title*" Then
Sheets("Results").Cells(Count + 1, 1) = Sheets(1).Cells(i, 1)
Count = Count + 2
ElseIf Sheets(1).Cells(i, 1) Like "Author*" _
Or Sheets(1).Cells(i, 1) Like "Source*" _
Or Sheets(1).Cells(i, 1) Like "Patent*" Then
Sheets("Results").Cells(Count, 1) = Sheets(1).Cells(i, 1)
Count = Count + 1
End If
Next

End Sub

 

https://forums.techguy.org/threads/solved-request-excel-help-either-with-formula-or-macro.1113943/
Relevancy 90.3%

Does anyone know if it is possible to use LastRow within a formula?

Example
Range("C" & Lastrow + 2).Formula = "=sum(C2:C)" & Lastrow
 

A:Solved: Excel Macro -> Using LastRow in a Formula

Range("C" & Lastrow + 2) = "=sum(C2:C" & Lastrow & ")"
 

https://forums.techguy.org/threads/solved-excel-macro-using-lastrow-in-a-formula.835805/
Relevancy 89.44%

Hello all I don t know if I m posting in the right forum This is an Excel problem I m having at work I m not overly Excel savvy but I know enough to get by at work and fix most problems myself I may just have some post maternity baby brain but I can t fix this problem My manager has a spreadsheet with about tables in each Spreadsheet macro/formula query Solved: Excel with monthly totals for different areas and each cell pulls data from half a dozen different spreadsheets and work books Pulling cell refs from so many things makes it slow on opening and management asked me to find a way to have individual months formulas calculate on the click of a button rather than on opening the spreadsheet I did a little test sheet which worked fine but when I did the same on managements spreadsheet it went a bit pete tong I recorded a macro to input the formula which when I recorded it is fine - all totals are correct but when I run the macro only half the formula appears I even tried a different method taking out sum so the formula doesnt work on opening and having a macro that simply pops that bit back in but half the formula disappears when I run it even though the macro doesn t touch the rest of the formula Any clue as to why this happens or am I just being a ditz and over complicating things Sorry if this is a bit confusing - I m not great with excel and I m now at home without the spreadsheet so can t go into much detail Ta muchly nbsp

Relevancy 89.44%

I need to come up with a way if the initials "pct" is found in a row, then a number in the same row is converted to a percentage (one decimal place). If the initials "pct" are not found, then the number will be converted to number (3 decimal places).

Would this be better using a formula or VBA?
 

A:Solved: Excel Formula Or Macro -> Change to Percentage

I think you need a UDF for this, or possibly VBA.
This link has to do with changing colors, rather than formats, but the idea should be the same:
http://www.ozgrid.com/VBA/ReturnCellColor.htm
 

https://forums.techguy.org/threads/solved-excel-formula-or-macro-change-to-percentage.838160/
Relevancy 89.01%

i would like to do the reverse of the attached macro

i would like to find and replace info in coumn c with the value of column b

column a=b

http://forums.techguy.org/business-applications/1032256-solved-macro-multiple-find-replace.html#post8195167
 

A:Solved: Excel Macro find and replace

The macro I wrote for you put the value from Column A which corresponds to the value in Column B in Column C
What do you exactly need?
If you want the value in Column B to replace the value in Column C then the result is the same as the source.
At least if I edit the macro to do that that is what happens.

below the original macro

Code:

Sub YourQuestion()
Dim rng As Range, rng2 As Range
Dim rVal As Integer

For Each rng In Range("B2:B" & WorksheetFunction.Max(2, Range("B" & Rows.Count).End(xlUp).Row))
rVal = rng.Offest(0,-1).Value
Columns("C:C").Select
Selection.Replace What:=rng.Value, Replacement:=rVal, LookAt:= _
xlWhole, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Next rng
Range("A2").Select
End Sub

the changed macro

Code:

Sub YourQuestion()
Dim rng As Range, rng2 As Range
Dim rVal As [COLOR="Red"]Variant[/COLOR]
For Each rng In Range("B2:B" & WorksheetFunction.Max(2, Range("B" & Rows.Count).End(xlUp).Row))
rVal =[COLOR="red"] rng.Value[/COLOR]
Columns("C:C").Select
Selection.Replace What:=rng.Value, Replacement:=rVal, LookAt:= _
xlWhole, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Next rng
Range("A2").Select
End Sub

I don't see the point of this but you're the one asking.
 

https://forums.techguy.org/threads/solved-excel-macro-find-and-replace.1043099/
Relevancy 89.01%

Hi all - I hope you can help I have a spreadsheet which is completed by a third party with columns of information Each day they complete a seperate sheet with this information and mail it over On the master spreadsheet I have got many tabs worksheets which I was hoping to combine into one master Required Macro/Formula worksheet which will detail every entry on all other worksheets Does anyone have a solution for me I also need it to update if a new worksheet is copied from another sheet and pasted in If this does not make sense here is an example - worksheet a contains rows of data cells across worksheet b contains rows of data worksheet c contains rows of data and so on I want a sheet to automatically take the data from these maybe more and give me rows worth of data I hope this makes sense - and that there is a solution out there Thanks Mark nbsp

A:Macro/Formula Required

hi mark,

perhaps this will go someway toward easing your workflow...
Code:

Sub Part01()
Dim R As Long
Dim N As Long
Dim i As Long
Dim SC As Long
Dim DATA As String
Summary = "xxxx" ' name of Summary tab can be changed here
Sheets.Add
ActiveSheet.Name = Summary
Sheets(Summary).Move before:=Sheets(1)
Sheets(Summary).Select
SC = Worksheets.Count
For i = 2 To SC
Sheets(i).Cells(1, 1).CurrentRegion.Copy Sheets(Summary).Cells(Application.Rows.Count, 1).End(xlUp).Offset(1, 0)
Next
End Sub

HTH
 

https://forums.techguy.org/threads/macro-formula-required.848827/
Relevancy 88.58%

I need a visual basic macro for excel that will do the following From sheet ColumnA Select rows until sheet columnA value does not start with REC as the no of rows is unbalanced sometimes rows sometimes rows sometimes rows etc copy selected rows and transpose to sheet columnA to however many rows were selected Repeat until end for 2002 to in new Visual columns Excel data unbalanced Basic rows sheet using Macro last row My real table has rows and the rows are unbalanced I am attaching a test file Please help me and thank you for your support and time Tech Support Guy System Info Utility Visual Basic Macro for Excel 2002 columns to rows in new sheet using unbalanced data version OS Version Microsoft Windows XP Professional Service Pack bit Processor Pentium R Dual-Core CPU E GHz x Family Model Stepping Processor Count RAM Mb Graphics Card ATI Radeon HD Series Mb Hard Drives F Total - MB Free - MB G Total - MB Free - MB H Total - MB Free - MB Motherboard MICRO-STAR INTERNATIONAL CO LTD G M -L V MS- Antivirus None nbsp

A:Visual Basic Macro for Excel 2002 columns to rows in new sheet using unbalanced data

Can you also attach a second workbook showing how the data should appear after the macro is run?

Regards,
Rollin
 

https://forums.techguy.org/threads/visual-basic-macro-for-excel-2002-columns-to-rows-in-new-sheet-using-unbalanced-data.1080037/
Relevancy 88.58%

G'day Magicians

I am an incompetent Excel macro creator and would appreciate some help.

I have a spreadsheet column with text in it
I want to move every second row two cells right and one row up
I want to delete every third row

Should be easy but I've just wasted two hours trying to do it can someone please help me tonight?

Forever grateful

Mike.
 

Relevancy 88.58%

Hi guys, first of all thanks for reading this.

I have a large amount of data that needs to be processed ASAP but need a decent macro to complete it - Ive tried but its beyond my abilities.

Essentially, I have a series of cells which contain information i need and information i dont need. I need to remove the non-essential data from each cell. An example of the data is as follows, each being one cell:

"O7853Ribulose-bisphosphatecarboxylase4.1.1.39Aa paleacea()()440GFKAGVKDYKLTY"

"Q8WLL4Ribulose-bisphosphate carboxylase4.1.1.39Abatia parviflora()()468KASVGFKA"

The part I need to lose is everything up to and including the double brackets "()()" leaving the number and sequence of letters.

Thanks to anybody who can help!
 

Relevancy 88.58%

Hi all I have a friend who s having a problem with a macro formula Solved: a selecting Excel cell macro: from a reference and Solved: Excel macro: selecting a cell from a formula reference it s been WAY too long since I ve worked much with macros so I told her to post here She s intimidated by you guys so I told her I d be her proxy but I ll send her a link to the thread and try to flush her out if you need clarification Basically she has a pivot table which produces a grand total When Solved: Excel macro: selecting a cell from a formula reference the pivot table is refreshed the grand total cell reference can change for example from D to F She has a formula that finds the reference of the grand total cell CELL quot address quot INDEX B AZ MATCH quot Solved: Excel macro: selecting a cell from a formula reference Grand Total quot B B MATCH quot Grand Total quot B AZ She would then like to have the macro select the grand total cell based on the reference provided from the formula so that the macro can continue on using that grand total cell reference I m sure I ve made a mess of the question so let me know if I m not making any sense Thanks nbsp

Relevancy 87.72%

I wonder if anyone can help with the following I have a table A V Peoples names appear from cells B B Dates are entered from C V with a range name defined as Date Integer values appear in cells C V I want a macro to be able to sort the table by selecting one of the dates and sorting in descending order of value against each person for the for macro Excel required Solved: problem sort a date just highlighted If I were to do the job manually I would highlight the table with the st Row of the highlighted area including the headings quot Name quot quot Date quot Solved: Excel macro required for a sort problem Date quot etc up to quot Date quot If I then clicked Data then Sort Solved: Excel macro required for a sort problem a dialogue box would appear Possible inputs to it reading from Left to Right would be Column Sort By Sort On Values etc and Order e g A to Z In the Sort By box a drop down list appears containing all the headings of each column This includes whichever date you require to sort the underlying values by So what would the macro script be to achieve the presentation of that dialogue box allow input to it and then activate the sort once the user had clicked OK I hope that I have made the application clear Help would be appreciated nbsp

Relevancy 87.29%

HI,

I have a huge product listing that i need to find and replace on a mass scale. I have one spread sheet that has 3 columns (see example below)
Column A****************Column B**********************Column C
1************************ M2123*************************M3455
2************************M3455*************************M3455
3************************M3433*************************M3455
4************************M6543*************************M3433
5************************M8793*************************M3433
6************************M6543*************************M7832
7************************M7832*************************M7832
8************************M4893*************************M7832
9************************M3938*************************M2123
10**********************M4837*************************M2123
*********************************************************M2123
*********************************************************M2123
*********************************************************M4837

In essence Column A is the id for column B (A=B), column C is longer than column a and b, because it contains duplicate (M)numbers. I would like to use the defined id in column a to replace the (M)numbers in columns c

So the spread sheet with the end result would look like this

Column A****************Column B**********************Column C
1************************ M2123*************************2
2************************M3455*************************2
3************************M3433*************************2
4************************M6543*************************3
5************************M8793*************************3
6************************M6543*************************7
7************************M7832*************************7
8************************M4893*************************7
9************************M3938*************************1
10**********************M4837*************************1
*********************************************************1
*********************************************************1
*********************************************10
Any suggestions?
 

Relevancy 87.29%

Hi Can someone please help I need Find in Excel Macro 2007 Solved: Replace and Multiple a way to be able to find and replace in Excel on a mass scale I have three colums A B C I want to be able to find the value from Column A and replace it with the value in the same row in column Solved: Macro Multiple Find and Replace in Excel 2007 B wherever it appears in Column C Column A Column B Column C Cat Dog www lynchie com cat UK etc etc www lynchie com horse uk www lynchie com Solved: Macro Multiple Find and Replace in Excel 2007 cat uk www lynchie com mouse uk Apologies for the bad rendering of an Excel Sheet This is a simple find and replace However I have approximately pairs in column A and B which need to be found and replaced in Column C and Column C is rows Is there any macro process which I could use or I m I doomed to do a find and replace times Any help suggestions would be hugely appreciated Thanks Lynchie nbsp

Relevancy 86.43%

Hello

I have a list that will always very in the number of rows. I want to copy a formula from F2 all the way down until the last information in column A. The formula I want it to copy down is =VLOOKUP(E2,'br vs cc'!$A:$B,2,"false")

So in my worksheet column A has info in A26(as well as 24 and 25). However there is no corresponding information in columns D thru H in rows 24 and 25. But I want the formula to copy to f26.

Can anyone help me with the coding for this?
 

Relevancy 86.43%

Hi all I m trying to create a form with the use of data validation so that staff do not key in wrong values The condition is The year of manufacture for a new insured cannot exceed years If the insured is not a renewal not new insured than the year of manufacture cannot exceed years The year of manufacture cannot exceed today s year Let me try to explain condition and with examples to clarify The year is A person wishes to insure his car this year his vehicle year of manufacture cannot be less than - years i e It can be anywhere in the range of to but below an error will occur prompting the user that we Data validation Solved: with formula cannot insure this vehicle The year is A person has insured his car with us in his vehicle year of manufacture cannot be less than - years i e It can be anywhere in the range of to but below an error will occur prompting the user that we cannot insure this vehicle I ve been looking at this for a number of hours and I feel it s only a simple formula to input in the data validation but I just can t figure out I ve attached Book xlsx for reference nbsp

Relevancy 86%

Hi there I don t have enough experience in VBA excel or word and would you mind helping for the following Q The below macro code add row below the last row And Would Data to sheet as Form Excel data doc as Import Entry database from word you mind telling me what is the Import data from word doc as Data Entry Form to Excel sheet as database code that can add row for word document table between the last row and the row before the last ActiveDocument Tables Rows Add Q My second question is related to export import between two different programs or files Excel and word document Would you mind providing me the proper code for Wordtable copy Workbook xls - sheet past speical value Suppose the word tables excel workbookD sheet are as follow Table in word document -------------------- Cell - Cell Cell - Cell -------------------- Table in Word document ------------------------------------ Cell - Cell - Cell Cell - Cell - Cell ------------------------------------ WorkbookD xls Sheet Rows Column A B C D wt Cell WT Cell WT Cell WT Cell WT Cell WT Cell WT Cell How I write a macro to make the value or text in Cell in word table to be copied and past in Cell A in sheet of WorkbookD xls and the value or text of the range from Cell to Cell of table or word doc to be copied and past in sheet of WorkbookD xls from range B for n rows in table and table of word doc Thanks Hamdy quot nbsp

https://forums.techguy.org/threads/import-data-from-word-doc-as-data-entry-form-to-excel-sheet-as-database.886270/
Relevancy 85.57%

G'Day Guys,
I'm wanting to automate a small spreadsheet I have, to put in a certain monetary value in a cell at the start of the month.

I made up a conditional formula and was able to get it to work, but of course, as soon as the next day ticks over, the formula is no longer valid, and the cell reverts to being empty.

Is there a fairly simple way to put something in place, so that once the condition is met, the value is retained?

My formula might not be ideal, and I'm happy to receive info on a better way to do this.

The conditional formula is: IF(A2=(TODAY()),A3,"") where A2 contains my trigger date, and A3 contains the Dollar value that I want to retain, once the formula gets a true condition.

Hope you can spare some time to offer advice.

Cheers Ron
 

Relevancy 85.57%

I am using Works 2004 and Word 2002 with the mail-merge option. I am using a .wdb file for my data for address labels but after I set up the labels format in Word and try to open the data source I get a message that says "Office cannot use Works files that contain formulas for mail-merge. Remove the formula from this file to use it as a data source. I have tried M/S help files Word and Works help files and still cannot figure out how to remove the formula. Any suggestions for an absolute novice in Word and Works. The data file was created in an earlier version of Works but I was able to merge it last year.
Thanks for any help.
 

Relevancy 85.57%

I have an external data range importing data (Access DB via file DSN) into an Excel spreadsheet.
My problem is date/currency fields in Access do not format properly after refreshing. Most of the time the fields default to a general.

Any ideas?
 

Relevancy 84.71%

months ago...
i installed AIRLINK, (a external wifi/adapter device) because my windowsxp internal adapter wasnt present.
after a day or so ,it cause my pc to respond very slowly...
so i so happen to check under ADMINISTRATIVE TOOLS, to PERFORMANCE, and i would always get the message:
""At least one data sample is missing. Data collection is taking longer than expected. You might avoid this message by increasing the sample interval.""

Any suggestions?...
windowsxp latitude d600
[email protected]
 

https://forums.techguy.org/threads/at-least-one-data-sample-is-missing.1022142/