Same Code Giving Different Results In Different Sheets?
Jun 5, 2012
So I am using a macro to do a particular task. Part of the macro's process is to delete some unwanted rows.
The code below was for my original macro which works absolutely fine:
Note: The ranges are fixed (i.e. column "AT" is reffered)
Code:
Sheets(1).Select
Dim Current As Worksheet
Dim Flt As String
Flt = Me.TextBox2.Value
For Each Current In Worksheets
With Current
With .Columns("AT")
.AutoFilter field:=1, Criteria1:="
View 3 Replies
ADVERTISEMENT
Jan 24, 2007
I have a problem with isnumber giving me two different results. One time it will evaluate the following as true:
ISNUMBER(0)
and in the same sheet it will evaluate the same as false.
I need ISNUMBER to NOT recognise "0" as a number all the time.
View 9 Replies
View Related
Feb 25, 2014
Cell A3 on my SHEET1 has the following formula =DATE(YEAR(C1),MONTH(C1) + 1,DAY(C1). It goes on like this for the next 12 months (+2, +3, +4....).
Column B on my SHEET2 has One of the five product codes (A, B, C, D, E) and Column D on my SHEET2 has manually entered dates
I created the following condition for each product code under each month in SHEET1
=IF(MONTH(Sheet2!$D$1:$D$39)=MONTH(Sheet1!$A$3),COUNTIF(SHEET2!$B$2:$B$39,"=A"),"")
The formula only works for two product codes (A and B), and then yields blank cells.
View 5 Replies
View Related
Jan 8, 2009
I am making a company wide estimate sheet and having trouble with the drop down box. I have a drop down to pick the branch, but when I name the source it only says branch, even though I named the data on the branch sheet. I would like to be able to pick the branch initials and have it populate the phone and fax number under the main heading but am getting a error there.
Also for the labor is it possible to have it insert the correct labor into the formulas at the labor lines according to which branch is picked from the drop down.
View 4 Replies
View Related
Jan 20, 2009
I have a VBA macro that I pieced together* a couple of years ago to help with a pretty simple task. We have two excel sheets that pull in information daily from different sources. The macro copies and paste values for the worksheet, then 1) saves a copy of the values only worksheet with the current date, 2) resaves the values only worksheet as a .csv file that is then manually uploaded into our database.
View 3 Replies
View Related
Mar 3, 2009
I created several macros objects to do simple hide columns, filter on, filter off, etc. type functions, using the Tools / Record Macro feature. Then discovered they won't work in a protected worksheet. Is there a way around this situation, other than turning protection off? Here's an example of the code for one of them, although I get the error or every attempt to perform a macro once the sheet is protected:
View 4 Replies
View Related
Jan 16, 2014
I can't seem to get why one code is giving a correct solution and the other one, not even close. They are both identical but there are some difference.
The program is suppose to pick up about 11 cell values, it then store these cell values into a collection, and randomly picking out the 11 values without repeating until there is no more to pick out.
Here is the code: (the top one doesn't give the correct result, it picks it out of the same ones and the number doesn't seem to store correctly, the bottom codes works as it suppose to work.)
Code:
lastCol = Cells(215, Columns.Count).End(xlToLeft).Column
Dim C As New Collection, I As Integer, rdom As Integer
rowFOUR = 216
colFOUR = 2
'cntHIT = 0
rowtest = 216
[Code] ..........
View 9 Replies
View Related
Jul 8, 2006
The following code is giving me an object-defined or application-defined error:
wsDst. Range("E" & NextRow) = "=VLOOKUP($B" & NextRow & ",PriceList!$A:$IZ,MATCH(HLOOKUP($I" & NextRow & ",PriceList!$C$3:$IZ$3,1,1,PriceList!$A$3:$IZ$3),FALSE)"
View 6 Replies
View Related
Jul 14, 2007
I am looking for VBA code by which the results can be obtained without having to run the code. For Instance, if Z = X*Y, I would like the code to automatically calculate Z for as soon as the value of X and Y are changed.
View 10 Replies
View Related
Jan 20, 2014
I am trying to do form consolidation. I have multiple forms to fill out for work so I have combined them into one work book and have all the cells of my paperwork linked back to a master page in the work book where i can fill out everything and it populates in rest of the workbook. From there, I export each page to its own workbook, save the file name and upload to server. But even after they are in their own workbook and uploaded viewers still get an error saying that it is linked to another sheet and asks if it wants to try to update. So far the only way I know how to fix this is after I export to its own workbook I have to copy everything, then Paste Special- Values to stop this. Is there any way I can have the sheets display only the values from those cells and not be linked in that manner?
View 1 Replies
View Related
Jan 24, 2007
Sheet1,
On this sheet I record the workers names and the time spent on one day, Each employee has a different rate and could do different hours, and also could come to this sheet twice. this sheet in the end give me, how much money I spent with each employee. Each employee is identified with a Number.
Sheet2,
On this one I record all downtime. Downtime's are identified with a Letter.
Sheet3,
Here I record the product's that we've done on that day, each complete product or part of a product is recorded in one row with quantity, elapsed time, product code (also a letter), in the end I know exactly how much time I used to do that product, and how many products from a specific code.
What I want is on the Sheet1, know how much time each employee and how many used to do "x" product. like this I can get the cost for each product and not only a total.
View 9 Replies
View Related
Aug 4, 2014
I am trying to sum the details from sheet2 in sheet3. I am currently using a plain formula to sum-up the values. However, create a indirect and vlookup function to sum the values.
In sheet3, I have to sum the values from sheet2 based on the contacts. Sheet1, sheet2 and Sheet3 are connected and any update(insert,delete.. etc) will be made in sheet 1 only. Sheet2 automatically updates based on sheet1. Similarly, Sheet3 should also update based on sheet1 and sum the values based on contacts from sheet2.
I have added the formula which I am currently using in the sheet.
View 14 Replies
View Related
Jan 24, 2010
i have a workbook with 30+ sheets and i want to create a new sheet containing a table from aprox 6 values from the others.
i have done this in the past by typing =(then select the cell from the sheet i want)
while this works it is very time consuming and filling down after i have done 1 row gives the wrong results
View 8 Replies
View Related
Jun 3, 2009
I have a work sheet with about 35,000 lines of data. Every day I have to sort the list by product category (I use auto filter to separate) and then copy the results into a new worksheet, there are about 300 product catagories. I'm new to macros but I'm wondering if there is a macro that can automate this process.
View 6 Replies
View Related
Jul 5, 2014
I want the following script to:
1) Find and define a range ("DateRng") based on cell values. This is controlled by the first block and the script does this job correctly.
2) Find cells
View 1 Replies
View Related
Dec 27, 2009
I want to name three sheets (sheets will have a different name every month so I refer to them below as Sheet(1), Sheet(2), and Sheet(3) (Their sheet index)) using a vlookup in VBA. I want the code to promt the user for input and based on the entry, I want to use a vlookup to name the sheets. There are three "lookup tables" on a sheet named "Ref" that I have defined as Table1 (used for naming Sheet(1)), Table2 (used for naming Sheet(2)), and Table3 (used for naming Sheet(3)) that I want to reference in the code. I also want a code to check the first MyLookUp1 which I have attemped below, but I do not know if I am doing this correctly. Below is my bad VBA attempt so you get an idea of what I am trying to do. Also, attached is an exaple file.
Sub namesheets()
Dim MyEntry As Variant
Dim MyLookUp1 As Variant
Dim MyLookup2 As Variant
Dim MyLookup3 As Variant
Dim MySTRING As String
MyEntry = Application.InputBox( _
Prompt:="Please enter an Item:", _
Title:="Lookup sheet name", _
Type:=2)
MySTRING = MyEntry
MyLookUp1 = Application.VLookup(MySTRING, Table1, 2, False)
If IsError(MyLookUp1) Then Exit Sub
Sheets(1).Select
ActiveSheet.Name = MyLookUp1....................
View 5 Replies
View Related
Jun 13, 2014
I have a workbook with 50+ sheets. Within each sheet are rows of data in column A that I'd like to search for specific text.
I'd like to search each row from every sheet for specific words (e.g. "7 days" AND "Monday" AND "Tuesday" etc.) then copy the entire row containing all my search text in a new sheet on column A along with the name of the sheet it was found in in column B and the row number it came from in column C.
What I am trying to accomplish is to search through all the sheets and post results in new sheets for each search string.
View 14 Replies
View Related
May 13, 2009
I have a worksheet named "List" with static values in columns A & B. I want to search all the other worksheets in the same workbook for the one that matches the value I entered in column B on the "List" sheet. The cell on the other worksheets with the possible match is B5. When it finds a match, I want it to take the data from cell B3 on that worksheet and paste it in Column C of the "List" sheet on the corresponding row (all rows in the "List" sheet have a different value in Column B).
In other words, when "List"b7 = worksheetb5 , then worksheetb3 is pasted to Listc7.
View 13 Replies
View Related
Nov 10, 2009
I have a wordlist (65000 words in Column B*) in a worksheet “w1” and poems (about 21000 rows) in another worksheet “w2” where first verse is always in B and second one in C. The column D of w2 contains of information such poems name, author’s name, book’s name and so on.
I want to search for those verses through a macro which contain words from my wordlist B* w1 and add references to my words that way. It’s about making a dictionary in which each word has a reference to a poem and verses in which it is used.
Column A of w2 has number in it which shows how many times these rows have been already used as reference. Column A of w1 contains of a value “1” or “0” in which “1” means this entry has already been processed or already has a reference and “0” means it has yet to be done.
If my word B* from “w1” exists in verses B or C of “w2” the results should be shown in an userform containing 5 text boxes with 5 results. In each text box a result should be shown “(verse B; verse C); (value of Col.A)” and a checkbox (or a button). That would mean 5 result, 5 textboxes and 5 checkboxes (or 5 buttons). I would like then to choose one of the results by checking the checkbox (or pressing the button) in front of the text box. After I have chosen the result the whole row from w2 should be pasted in following columns of B*. That means the cells from w2 column B,C,D would be pasted to column C,D,E of w1.
The criteria for the search in B and C should be the lowest value in A of “w2”. I mean the w2 rows with lowest A value should be preferred if there are more results. The result showing user form with 5 text boxes and 5 checkboxes(or 5 buttons) which gives me the choice to choose one of the results should also contain a button “search for further results“. For the case none of the results is useful.
As far there are many poems (verses) I would like to prevent using the same verses many times. The more different verses I use as reference for the words in w1 the better it is. Therefore every time a result is picked by me “1” should be added to the value in Column A in w2. And every time search is started the lowest A values should be searched first.
As soon a word has successfully got it’s reference the value in A w1 should be changed from “0” to “1”. And by next search all words with A value “1” should be ignored and only “0” words should be searched for.
Each time a row from w2 is used as reference, the B* word w1 should be added to col. E of w2. If used for many ";" should be the seperator.
View 14 Replies
View Related
Feb 2, 2009
I want to locate the corresponding acct number between worksheets “primary_data” and “qty_movement” and once a match is found (from acct worksheet) then copy over the acct numbers and the share data to the results page. If there is no match I don’t want anything copied to the results page, just ignore that data on either worksheet “primary_data” or “qty_movement”.
•Both ranges on “primary_data” and “qty_movement” worksheet are variable, as accts are left off or kept on depending on the daily activity so the ranges are never set.
•I need to concatenate on “primary_data” worksheet cells A&B&C to get acct number
•concatenate on “qty_movment” worksheet cells B&C (need to keep zeros in front of actual numbers for acct number reasons)
•these accts need to be cross referenced as the accounts on the “acct” worksheet (pre-populated with corresponding acct numbers that never change and will always be on that worksheet)
•If a match is found on the “acct” sheet then I would like it to either replace the acct so they match on both worksheets or just to recognize those accounts correspond with each other and do the below.
•then take the 2 concatenated acct numbers off of “primary_data” and “qty_movement” in the I cells and copy them over to the “results” worksheet as well as the share data from “primary_data (column E)” and “qty_movement(Column D)” and then compare the 2 share amounts on “results(column E)”
i need to elaborate on anything, the attached sample sheet shows what i want to do, but without any formulas or code.
View 5 Replies
View Related
Nov 30, 2009
The data to be filtered is in several sheets, and once filtered is to be copied to a destination sheet (in this case "Temp"). The criteria for advanced filter is on an altogether different sheet (in this case "Reports"). The macro is actually simplified for the purpose of the question, and I want to re-use the code several times, hence the use of variable "filterRng". When I run it, I get the subject error at the bolded line in the code below. I'm thinking that the Advanced Filter doesn't like a variable as a range reference, as it runs perfectly well if the commented out line below the problem line is used instead.
Sub Test()
Dim i As Integer
Dim rngData As Range
Dim filterRng As Range
Set filterRng = Sheets("Reports").Range("A121:K124")
Application. ScreenUpdating = False
Application.DisplayAlerts = False
View 4 Replies
View Related
Jun 24, 2014
I need to do VBA coding. Got 12 Sheets for 12 month of Sales. Every Sheets are in same Header Format.
For Column R (Status), there's Filter Data "TRUE" and "FALSE". I have to move/copy "TRUE" item into Sheet Aging 2014.
I manage to transfer using only one Sheets using Advanced Filter VBA, failed with other Sheet.
I attached the file : Sales 2014.xlsx‎
View 5 Replies
View Related
May 24, 2014
I have a workbook that contains 3 sheets. What i am looking to do is:
1. Use the names in Sheet 1 (Column A) and find the names on Sheet 2(Column E).
2. If there is a match, put the whole row that contains the match on Sheet 3
View 14 Replies
View Related
May 11, 2008
I load an text file into an excel sheet and after some eventually changes I save (overwrite) it back into the text file again. Now the text file has become a file with 50 pages (49 empty pages). How can I avoid this or how can I set an EOF when no more written lines follows. The code for saving the excel sheet is this:
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Sheets(wsImport).Select
Sheets(wsImport).copy
ActiveWorkbook.SaveAs FileName:=WorkFolder & WorkFile, _
FileFormat:=xlText, CreateBackup:=False
ActiveWorkbook.Close
'ThisWorkbook.Activate
Sheets(wsKwartaal).Activate
View 6 Replies
View Related
Jun 15, 2008
When stepping through my macro it moves through each line of the code showing me the code as it steps through. Is there a way to step through and see the results of the code as it is going through. I have an "IF THEN" statement in the macro that is not giving me any results so I think I must have an incorrect reference in the code, but I can't see it. I think if I could watch what it is supposed to be doing as I step through I might be able to find the error.
View 4 Replies
View Related
Apr 4, 2008
Here is my Macro:
Sub monthdatecoding()
Dim lastrow As Long, t As Long
lastrow = ActiveSheet.UsedRange.Rows.Count
For t = lastrow To 1 Step -1
'June
If Cells(t, 1).Value "" Then
If Left(Cells(t, 13).Value, 2) = "6/" Then Cells(t, 41).Value = "June"
End If
'July
If Cells(t, 1).Value "" Then
If Left(Cells(t, 13).Value, 2) = "7/" Then Cells(t, 41).Value = "July"
End If
View 9 Replies
View Related
Aug 9, 2007
I have created a macro some time ago that is an integrated part of an XLA. The Xla has worked fine but now, for some reason, the macro fails to import the specified text, it doesn't fail but nothing gets imported. I have tried solving this myself, but alas I am not bright enough
The code is:
Sub GetWorksheet()
Dim filetoopen As String
Dim wb As Workbook
filetoopen = Application _
. GetOpenFilename("XL Files (*.xls), *.xls")
On Error Resume Next
View 5 Replies
View Related
Mar 20, 2013
In an excel sheet, I am using a macro to filter. Macro code is as follows. When I run this macro, it returns 4 rows matching the criteria from 560 rows in the excel sheet.
VB:
ActiveSheet.Range("$A$1:$I$566").AutoFilter Field:=1, Criteria1:= _
">=11/11/2012 22:13", Operator:=xlAnd, Criteria2:="<=11/12/2012 6:47"
I want to use the same criteria in the vb.net code, to do same thru vb.net. But, when I run the same code in vb.net, it just returns only 1 row (1st row only).
VB:
[FONT=Consolas][SIZE=2][COLOR=#0000ff]xlsRange1.AutoFilter(Field:=1, Criteria1:=">=11/11/2012 22:13", Operator:=Excel.XlAutoFilterOperator.xlAnd, Criteria1:="<=11/12/2012 6:47")
This returns 1 instead of 4 expected.
View 2 Replies
View Related
Jan 23, 2013
I have the equivalent of Vlookup code(I couldn't get vlookup to work correctly) I am using Excel 2007. I have an array 2 cols x 1652 rows. I have another column of 6264 items. If a text item in the first column of the array matches a text item (minus 3 characters) in the longer column, I place the matching row from the array next to the item in the longer column. Whats not working is that the text never gets put in the cell.
Code:
Sub Macro1()
'
' Macro1 Macro
[Code]....
View 7 Replies
View Related
Dec 13, 2006
I must be using hte wrong command or I am dumb.
Cell B8 I have a validation list of either Hub, Router, N/A
On cell C8 I want if (B8=Hub,3COM,0) but I get a NAME error. Is it because it won't work with letters but it can make the calculation?
View 9 Replies
View Related
