Not sure what i'm doing wrong but I have this bit of code to get an average of a set of data. then i'm planning to paste that avearage into another sheet. however I cant seem to get the variable to work. without the variable it works:
View 3 RepliesI have a front sheet with a list of all the sheets in the workbook in column A.
In column B I need to use the counta function to count the number of entries in the corresponding sheet to column A.
As there are 70 sheets I'd thought I'd use a Macro but I'm having great difficulties. I've done a search but can't find what I'm looking for.
So heres part of my code (it uses loops etc which I've managed but this just errors)
cell.Offset(0, 1).FormulaR1C1 = "=counta(" & str2&" C[-1])-1"
The str2 is the string name to reference the sheet in the formula (for example sheet1), I can't figure out how to enter this into the code so it works and is accepted.
Sheet 2 has 3 cells with values:
C14, C15, C16
I need VB code to:
copy the values in those cells
Return to Sheet 1, let the user click a cell in any row in Colum F, then Paste them (Special, Values and Transpose)
I am trying to limit the amount of stuff I write to a sheet, and I figured I can just pass variables around inside of a routine with out having to write each step to a sheet like I am doing now.
Below is the formula showing Z2 as where parsed data is
CVersionFormula = Evaluate("Z2 * 10 ^ (4 - Int(Log(Z2)))")
and here it is, with me trying to plug in a variable rather than read it from the data sheet. The variables for ease are read from the data sheet. In this example I would read in Z1 and assign it "CVersion", then the formula then takes CVersion (a 4 digit number) and makes it a 5 digit number..
CurrVersionE = Evaluate("CVersion * 10 ^ (4 - Int(Log(CVersion)))")
But I don't think I have the variables in there right.
I have some problem with the extraction, I would like to extract the data to another sheet based on variable conditions but I can not do it. My code in just case working if the all conditions are specified. The target is that if want the code could extract the data based on one condition even, or two or three or more.
View 7 Replies View RelatedI have a written a macro to populate a filter criteria with a comma separated list, but the filter fails to work as each individual item on the list needs to be surrounded by speech marks (") and I don't know how to do this.
This may not be an actual vba issue?
The list I am using is derived from =SUBSTITUTE(TRIM(BK9&" "&BK15&" "&BK21)," ",",") There are 120 different cells that this formula references that could be blank. The SUBSTITUTE TRIM functions allow me to just use the cells that have data in. But I can't seem to get the " symbols in there. (I've tried the TEXT function on the original cells, & this doesn't work.
I'll do what I can to explain this mess I want to clear up...I have a series of excel reports I have to download and work thru daily and I need to see if there is a code to autofill down thru a column, based on multiple statements throughout the column..here goes:
f1 msp
f2 msp
f3 blank **
f4 mct
f5 mct
f6 mct
f7 blank **
f8 cci
f9 blank **
Regardless of the actual f cell, I always will need to fill the Blank ** cell w/ the values from the previous cell....Hope this makes sense...& thanks in advance....
have_a_cup@cox.net
I have a small data set (ends up with 8 columns, up to 1000 rows), that gets imported sorted and formated via a macro linked to a button. I need to be able to look at one particular column of info, which will contain different text values every time the data is updated, and create a list of all the different values that occur in that column (maybe 10 max). Then...... I need to calculate a numerical value from adjascent column, linked to the text values from the first process. Below is an example to better explain:............
View 2 Replies View RelatedI'm fairly new to macro's and VBA, by searching on the internet i've copied and pasted some code together into a macro.
But it ends in a Run-time error 91...
The macro opens a target .xls file in a selected folder, performs copy - paste actions from masterfile to targetfile.
Than it filters data in the targetfile sheet1 and copy's the results to the various other sheets; saves and closes the targetfile.
The next target file in the folder is opened and the actions are repeated in this second target file.
For the first target file this works smoothly; but for the second one (of a total of around 100) it does not copy the filter results to the other sheets in this workbook.
The error message i get is: "Run-time error 91:Object variable or with block variable not set."
When i hit debug it highlights the line "ActiveSheet.Next.Select" which, at least in the first file, seems ok.
I have attached a 97-2003 .xls file with data for multiple store locations on sheet 1, and the desired result on sheet 2. I am actually using excel 2007, but I dont think I need any special features that it provides.
I will try to explain the issue here without opening the attachment.
Here is an example of the Data on Sheet1
i need a macro that will compare the p'folios in sheet "Data to Sheet Workings, and then list the p;folios which is in sheet Data but not in workings in Sheet "New", so in the example below this would be ETSTSA ...
View 9 Replies View RelatedI need a Macro which pulls the data from different sheets of excel (which is not formatted properly) to Main Sheet. Also some of the columns will not have the same names, so macro should handle this exception as well.
View 8 Replies View RelatedWhen i try to run the code below i get the error message - object variable or with block variable not set-
Sub REFRESHXX()
'LIST
Cells(Sheets("POINTS").Range("DD801").Value, Sheets("POINTS").Range("DD800").Value).Select
Selection.AutoFilter Field:=1, Criteria1:="1"
'SET RANGE
Dim sFormula1 As String
Dim sFormula2 As String
Dim sCell1 As String
Dim sCell2 As String
Dim sSheet1 As String
Dim sSheet2 As String
Dim r As Range
Dim MyRange As Range 'for testing
With Sheets("Points")
sFormula1 = .Range("CY1").Formula
sFormula2 = .Range("CY2").Formula
End With
'FORMULA IN R1C1 STYLE
strFormula = "=IF(ISNA(VLOOKUP(RC[-1],MASTER!R4C3:R17908C7,3,FALSE)),0,VLOOKUP(RC[-1],MASTER!R4C3:R17908C7,3,FALSE))"
'ENTER FORMULA IN ALL CELL RANGES
r.FormulaR1C1 = strFormula
'REDUCE TO VALUES
Dim ar As Range 'an area is a range
For Each ar In r.Areas 'areas are discrete, contiguous ranges of cells
ar.Value = ar.Value
Next ar
'UNLIST
Cells(Sheets("POINTS").Range("DD801").Value, Sheets("POINTS").Range("DD800").Value).Select
Selection.AutoFilter Field:=1
End Sub
I found this nice little bit of code for a date range search in column A but it will not work. Apparently i have not set a variable or something.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim startDate As String
Dim stopDate As String
Dim startRow As Integer
Dim stopRow As Integer
startDate = InputBox("Enter the Start Date: (dd/mm/yyyy)")
If startDate = "" Then End
stopDate = InputBox("Enter the Stop Date: (dd/mm/yyyy)")
If stopDate = "" Then End
startDate = Format(startDate, "dd/mm/yyyy")
stopDate = Format(stopDate, "dd/mm/yyyy")
startRow = Worksheets("sheet1").Columns("A").Find(startDate, _
LookIn:=xlValues, lookat:=xlWhole).Row
stopRow = Worksheets("sheet1").Columns("A").Find(stopDate, _
LookIn:=xlValues, lookat:=xlWhole).Row
Worksheets("Sheet1").Range("A" & startRow & ":A" & stopRow).Select
End Sub
I need to create a macro that will scan a spreadsheet for the number of sheets and then pull data from the same places on each sheet in order to create a summary sheet. Let me try to explain a little better.
The spreadsheet I'm working with has a separate sheet for each new deal our company makes. Each of these sheets is in the same format - we use a template and fill in the data based on that whenever a new deal emerges. The sheets contain basic info about the deal in the first few rows and columns, then some narratives with dates describing the progress of the deal, and then a list of issues and whether or not they have been resolved. The problem is, each of these sheets contains too much info for a quick, high-level overview with the bosses so they've asked me to create something that will pull the basic info, the most recent narrative, and any unresolved issues from each sheet. This way, each time there's an overview scheduled with the bosses, the macro can be run and it will create a new sheet with data from each sheet in the workbook.
I attempted to modify "macro_1a1ay" to look into the "comments" sheet (column a) for a specific text string. If that text string is found, I have it delete the entire row, then re-sort the page and return to the calling page. It works well as long as it finds something. When it does not find the string (i.e. like now when the page is blank) it gives me the run-time error mentioned above.
View 7 Replies View RelatedI'm trying to get the data from Receipt log (sheet 1) to automatically populate into the Print Receipt (sheet 2) and to automatically filter and go to the Activity by account (sheet 3). I am so new to this and totally lost.
See attached sample : Student fees.xlsm
How to create macros. I need to copy a certain group of cells from one sheet to another, and then do it for x number of times. I'm just using the record function and now i'm lost. Here is the code i currently have:
Sheets("Mapping QTR2").Select
Range("B137:D230").Select
Selection.Copy
Sheets("ExpDB_Q1").Select
[Code] ....
Also, column C is blank. i would like to get the values from another list in the same workbook, say "branch list". I would like to populate Column C with one specific branch for each "batch", if that makes any sense...
I am looking for a Macro which can transfer data from sheet 1 to the first available empty row on sheet 2.
For example, I have data in the following cells on sheet 1: B2, B4, B6, B8, and B10. I want to transfer this data to the first available empty row into cells A1, B1, C1, D1 and E1 of sheet 2. The cell number is ofcourse depending on the first available empty row and could thus be 2, 3, 4, 5 etc.
I also want to delete the data from the original cells in sheet 1 after the data has been transferred.
I have a couple of spreadsheets that requires me to copy data from Sheet 1 that matches certain criteria to Sheet 2.
I have tried using posted VB codes that have been written for other people but they dont seem to work (this is probably to do with me not actually knowing what I am doing, as I am not sure what I should be replacing and what I need to be deleting etc out of the pre-written code )
I need to be able to search on column e in Sheet 1 for anything that has just h written in the cell and then copy all the rows that match into Sheet 2. I also would like to ensure that when it is copied it doesnt go in row 2 but maybe 4 or 5 as I have quite allot of headings.
I am trying to create a macro so that when i save an entry the form sheet looks in the tavela master sheet to see if there is a match on the record number (in the A column) and saves there as an update if it matches and then if there is no match, saves it in the last empty row. this is a column to row copy so it has to be paste special to transpose. also i have a password on the sheet which i use to unprotect then reprotect. i have successfully done these two things separately but cannot combine them! i know not very much about visual basic code just what i have read here and so i am trying to make this code work.
Code:....
I have two worksheets. Sheet 1 has 2 columns, Column A the restaurant's name and Column B contains the review score. So sheet 1 is kinda like this:
Restaurant |Score
Ruby Tuesdays 80
TGIF 78
Outback 92
Sheet 2, Row 1 column B-E contain restraurant names (only on the top row, like field names).. i.e. I manually put the date in because typically the projected date is different from the actual review date.
-A----------- B ----------------C ------D-------- E-----
Date |Ruby Tuesdays|Olive Garden|TGIF|Ruths Chris|
I need the data from Sheet 1 Column B moved to sheet 2 in the next open row (i currently have data in row 1..the field names and down to row 35). This will be continuous so each time i need it to add the score as a new row in the correct field (restaurant name), IF the restaurant isnt listed, I want a new field named with the restaurant name and then place the score in the correct row and column. So, in the example I'd need Outback added.
I am working on a project and would like to see if there is a solution for it. i have a workbook that has data entry that is summarized at a master level but I need to automatically use a formula to update another sheet after clicking an udpate button. The data from the data entry sheet needs to be allocated to all the lines that has the same master item based on the formula. A test workbook is attached ...
View 1 Replies View RelatedI have a worksheet which contains certain sections. I want to create a macro which will run if data is input into those sections. This macro should copy whatever was entered into another worksheet automatically as data is being entered. Is there a way for that?
View 8 Replies View RelatedI'm trying to move data from a primitive user form to another sheet acting as a DB. I will further pivot the data in a third sheet to boil up results.
Here is the primitive user form - or desired data from the user form: ...
I'm working on spreadsheet to track my sales on Amazon.com. In doing so, I need to create a single worksheet (called "Ship Master")that lists every sale I've ever made. I am able to generate reports on Amazon's website which lists all of my sales for a given period (up to 30 days maximum), which can then be copy/pasted into my workbook.
In theory, if I was very vigilant in selecting the dates I chose to download, I could simply copy/paste the data right into the end of the "Ship Master" worksheet, but mistakes could lead to redundancies and errors in my data.
What I'm looking to do is to download a report from Amazon, and after deleting any old data from the "Ship Import" sheet, pasting the fresh data into it.
From there, I'd want the macro to look at the newly pasted data in the "Ship Import" sheet, and copy any new sales into the "Ship Master" worksheet. Both worksheets have identical headings. There is a unique identifier in column D of these work sheets called "shipment-item-id". This identifier is what the macro is going to be checking for when it determines whether or not the data should be appended to the "ship master" worksheet.
I've attached a sample workbook with the 2 worksheets and some data in them.
I wanted a Macro to get paticular cell value from 1 sheet to another For Eg the I have the below data is in 1st sheet
NG
Article #
Store #
DC
[Code]....
I have the below data in 2nd Sheet
So I want a Macro to find the delivery date considering the Combo of Site and DC from 1st sheet to find the exact delivery date of that Site and DC combo from 2nd sheet and the date should be in YYYYMMDD format
Site
DC
Loading Group
Week #
[Code].....
In need of a macro to change the look of the attached example spreadsheet.
View 3 Replies View RelatedI have a project that I'm going to attempt to handle, unfortunately I have no idea where to start. So any ideas or input to get me in the right direction would be greatly appreciated!! I am new to macro programming in excel and have a very light background in programming.
What I have:
Right now I have a data file(Sheet1) that lists the data of various accounts. Each row is a different account and each column is data that pertains to the account. Ex. Row 1 will be account # 1, with things like name, account number, address, etc... in columns A B C and so on.
What I want to do:
I need to make forms for EACH account in another sheet (sheet2), and in that form i need to reference specific data from Sheet1. THe reason it needs to be referenced is beecause if we make changes to Sheet1, the form will also need to be updated. Also, I dont need all the data in sheet one for each account, I will be pulling out data from different columns, ie. only columns A, E, F, etc...
I import a raw data file from our phone system into sheet 'imported', I then want to display the data on sheet 'formatted' how I have displayed it for the purposes of this example. I had a solution to this but the formatting out of the phone system has changed.So I need it to list the agents names, and then the corresponding time for each activity. The activities are listed on B3:I3.
View 2 Replies View Related