Sub do_it()
Dim x As Range
Set x = Columns(4).F ind("*test*", lookat:=xlWhole)
If x Is Nothing Then GoTo 999
r = x.Row
Cells(r, 1) = Cells(r, 1) & " (W)"
999
End Sub
I need the above code to continue down column 4 and add the " (W)" to the entire column.
The code stop when it finds the first match.
We know how INDEX/MATCH works, and it's very nice. I attached a COUNTIF to it to count how many times the index finds itself on another table; if it doesn't find itself, then it goes blank. However, this time I need to count how many times it finds a certain string condition in the other table.
sampleexcelhelp.xlsx
The columns that need to be filled are shaded in dark pink.
I'm looking to have a row at the top of a worksheet which I can type in, so that only the rows below which contain that information will show up. For example, say I have the following 3 rows, 2 columns each:
Cat Feet
Cat Head
Dog Feet
I'd like to have an additional row so that if I typed in "Cat" only the "Cat Feet" and "Cat Head" rows would show up. Likewise, if I typed in "Head" in the proper column only ""Cat Head" would show up.
What is wrong with this formula? I have used it before just fine, but sometimes including now it won't work.
PRODUCT_IMAGE Images 244-447439 244-455507 244-455507 244-455508 244-455508 244-455509 244-455509 244-455512
=MATCH(B2,$A$2:$A$412,0)
We can clearly see that we have a match in rows 2 and 1 columns A and B, but I am getting #NA. I have sorted the columns A-Z as well. I have also tried formatting the columns different ways and I still get #NA.
I need to conditionally format column B if it finds a match in A.
In Column A I have first names, In column B I have last Names, in column C I have id letters,
Column D another list of First Names And In Column E I have another list of Last Names
So what I need to do in F2 is Look at the name Last name in E2 (Lets say its Smith) then look down the Last names In Column B when you find a match look at the First name on the same row to see if the first 3 letters are the same as the first 3 letters in D2 if they are then put the id that's in cell C into F2 if not ""
I've been trying for hours but no luck, also if you do manage to do it can you tell me how you get it to look at the first 3 letters and how I could change that to 4?
I am working on a data entry sheet time and I am running into a wall. I am trying to code intelligence into sheet so that the user doesn't have to enter as much data.
I am using Excel for Mac, 2011, 14.2.3, But I would like to be able to have others use it on PC and in Excel 97 - 2004
I am developing a time sheet for work. I am trying to have excel generate the work order # for subsequent jobs.
'Daily Entry Sheet'!$B$8:$B$138 = Date
'Daily Entry Sheet'!$F$8:$F$138 = Property #
'Daily Entry Sheet'!$G$8:$G$138 = Property Location
'Daily Entry Sheet'!$H$8:$H$138 = Work Order Number
Following is what I presently have coded.
The following Works but is not completely as I need.
=IF(OR($F8=0,$F8="",ISNA(G8),$H7="Work Order #"),"",IF(ISERROR(INDEX($H7:H$8,MATCH($F8,Prop.,0))),"",INDEX($H7:H$8,MATCH($F8,Prop.,0))))
This works fine in that the match finds the first instance of the work order #. The problem is there may be a later worker order # for the same property which supersedes the old work order. I need to find that last work order number for property # 919 for example, not the first instance, as the index/match returns.
I looked for a function similar to the sumif or countif
=SUMIF(Prop.,$F4,WorkOrdNum)
=SUMIF(Date,"="&$V6,Total_____Hours)
But I don't need to add the work order numbers.
Countif can give me how many instances property # 919 is used,
But I haven't figured out how to use that to It would be ok if the function would return the largest number like the MAX function, best would be to select based upon the latest date.
I need a piece of VBA code to assign to an Excel form that determines the maximum value of a subset of one column whose cognate rows in an adjoining column satisfy a particular value.
View 2 Replies View RelatedI'm looking to use a do until/loop code to find a cell that equals a named range ("Clause") that is located on another worksheet, the code I have so far is:
[Code] ......
I've used something similar before and works, but I just cant get it to work. The values that I need to find the match to the ("Clause") cell are directly below the original activecell.
I created a user form that has two textboxes on it. One is to enter a person's name that gets put into column A, starting at row 4, and the other is to enter a number into column B, starting at row 4.
What I would like to do is for the form to search the names that have been entered in column A for duplicates in the same range. If the name is not there already, then I would like it to operate as normal. If it already exists, then I would like the number in the text box to go into column C in the same row as the name.
Here is the code I have already on the Submit button:
Private Sub SubmitButton_click()
Dim emptyRow As Long
Sheet1.Activate
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 4
Cells(emptyRow, 1).Value = Guestname.Value
Cells(emptyRow, 2).Value = Roomnum.Value
Unload Name_usrfrm
End Sub
Is this possible? I have to make it more complicated, but once I get the basics, I might be able to figure it out myself.
I am working on a formula that, eventually, I would like to be inserted into cells using VBA's FormulaArray Range function. Unfortunately, in its present state, the formula is too long and clumsy to meet the 255 character limit on that function. While I know that I can easily shorten the current formula with simple adjustments, such as using named ranges, I still am forced to ask whether the design of the formula itself could not be improved.
The heart of the formula is an Index/Match combination which will search if any line on the 'Archive' sheet meets the three criteria on the line specified in the 'Data Log' sheet. If so, it returns the value from a particular column of the matching row on the archive sheet. If not, it returns N/A.
=INDEX(Archive!$E$1:$E$1999, MATCH(1, IF(Archive!$B$1:$B$1999='Data Log'!$A2, IF(Archive!$A$1:$A$1999='Data Log'!$C2, IF(Archive!$D$1:$D$1999=1,1))),0))
In an effort to circumvent this unsightly N/A result I have attempted to use the ISNA function paired with an IF statement. The idea is that if the result is N/A then "Pending" will display instead. Otherwise the result of the indexing formula will display. This approach is outlined below:
=IF(ISNA(index function), "pending", index function)
The problem, of course, is that the indexing formula is rather long, and, with this approach, I am required to put its entire contents in the formula twice. I would greatly appreciate any help in streamlining this formula. Remember, the final goal is simply to search the 'Archive' sheet for a single line meeting three criteria and return a value from a fourth column, or if not return "pending".
I have this equation
=(INDEX(abc!B8:D18,MATCH(Sheet1!C39,abc!A8:A18),MATCH(Sheet1!D39,percentiles,0)))
it gives me this #N/A. I'd rather have it show $0.00.
I'm trying to create a IF function to stop at $1500 for 401k employer match. To make it simple the variable in column D is constant but in reality it's based on 50% match amt up to $1500 of amount contribute(% of gross pay). The accumulation of matching is a rolling balance. If the match amt is less 1500, then match amt. But False, the left over amt equal 1500 then STOP there onward.
IF(SUM($D$3:D19)+($C20*0.5)<1500,$C20*0.5,SUM($D$3:D19)+($C20*0.5)-1500)
is there anyway to stop screen scroll?
so that you can only see the visible cells at opening of workbook?
I use a code in ThisWorkbook to always open on sheet 1 when opening up the excel spreadsheet.Is there a code that I could put in to ThisWorkbook that would do a check that the workbook was e.g. "WorkbookA". If it wasn't then the start up code would be cancelled/ignored.The reason for this is that I am copying this workbook whenthe user has entered details in to it and so I do not want this code to run in the saved workbook, that will have a different name.
Workbook A is the master copy or template
I have the below code (pieced together and donated by other users on the forum)
Application.Calculation = xlCalculationManual
'
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim Ctr As Long 'set ctr for Invoice
Dim wsData, wsInv As Worksheet
Dim Ibk, Rbk As Workbook
Set Ibk = Workbooks("Invoice.xls") 'Invoice as Ibk
Set wsInv = Workbooks("Invoice.xls").Sheets("Invoice")
Workbooks.Open Filename:="\cmc-dc01usersdcMy DocumentsProjectsRemake GoodyearRentCharge.xls"
Set Rbk = Workbooks("RentCharge.xls")
Set wsData = Workbooks("RentCharge.xls").Sheets("Sheet1")
With wsData........
I have written a code but i can not make it stop where i want it to stop. I would like the code stop when cells(31,4).value reaches 0. Pls refer to attached sheet for a clear example.
The code is as follows
Sub production2()
If Cells(35, 4).Value > 0 Then
Cells(31, 4).Value = Cells(35, 4).Value
For j = 5 To 16
If Cells(36, j).Value < Cells(29, 4).Value Then If Cells(31, 4).Value > Cells(29, 4).Value Then Cells(37, j).Value = Cells(29, 4).Value
If Cells(36, j).Value < Cells(29, 4).Value Then Cells(37, j).Value = Cells(29, 4).Value
Cells(31, 4).Value = Cells(31, 4).Value - Cells(37, j).Value
Next j
End If
End Sub
I enter 1 record using the form, the next records does not populated correctly. It looks like the code just keeps looping by ....
Trying to get my code to loop for one minute. Have tried this code (after searching through a few threads):
Sub LoopUsingTime()
x = 1
EndTime = Now + TimeValue("00:01:00")
For lCount = Now() To EndTime
Sheet1. Cells(x, 1) = "the time is now " & Now()
Sleep 5000
x = x + 1
Next lCount
MsgBox ("loop finished")
End Sub
I am trying to create a graph for a range of data that updates monthly (adding an extra month each time). I wanted the graph source data to update automatically each time the data is refreshed so used an OFFSET formula to identify a named range. I then point the graph to the named range as the source data.
When I enter the range as the source data the graph picks it up. However, when I re-enter the source data option on the graph it has converted the named range into a cell written range (ie. replaces "=QUALITY" with "='Front page'!$B$7:$J$10" - which therefore will not update when the range increases.
Attached is an example of the program. The purpose of this program is to allow someone with very little knowledge about the information to enter data. The first tab labeled "Instructions" is will have two macros assigned to the buttons at the bottom of the page. The first macro labeled "Move Data" will move data will clear data in the shaded cell on the "Entry Form" sheet and fill those cells in with '----. The second macro labeled "Generate and Save" will save a master copy of the program, save a historical copy of the program by date, and print/save a pdf copy of the "Reports" sheet.
Each one of the historical (the last 8 sheets of the program) sheet receives data from the "Entry Form" and then moves the data by date into the table for archiving purposes.
If you look at the code for the "Entry Form". I have a couple of subs. The first being Worksheet_change. the first section of this code is to force certain cells to be uppercase. This part of the code is working fine. The second part of the code is the Dim MyValues. This part is working fine also, but...
My issue is... When I run the move data macro from the "Instructions" sheet the Dim MyValues reads these as changes to the cells and wants to push the data to the historical sheets.
What I would like is an IF Statement above the Dim MyValues part of this code that stops the Dim MyValue part of the code running if those values are blank, 0, or have '---- entered into them.
Test.xlsm
The code that I'm using is supposedly not allow spreadsheet to be opened after a certain date, or does not allow a user to press any of the command buttons and comes up with a message saying the spreadsheet is closed for new entries.But is not working for me.
I placed the Code in the ThisWorkbook module.
[Code].....
I have the following code to add dash mark to TextBox1 value depending on some condtions (mentioned in the code), the code works perfectly, but when we use Backspace Key to clear some charactors it is conflicting with the code running when changing Textbox1 value.
View 11 Replies View RelatedI have created a few macros and once I was happy that they worked independently I joined them to run as one macro in several steps. I have just discovered a bug ...
The macro filters on a column and copies and pastes that range into another sheet.
HOwever, sometimes (as I have just discovered) when it filters, there will be no data against the value it is looking for.
I need to say, if no value, move to next step.
I.e. here's my code. It's looking for ASIA and it will copy and paste all rows with Asia to another page. It then does the same for EMEA. Thing is, in my example just now, there was no EMEA data and some times there may be no ASIA data so I need to build in some rules that if it does not find either of these it continues to finish macro or pops up and says 'NO ASIA found, CLick to continue' or something?
Selection.AutoFilter
ActiveSheet.Range("$A$12:AA" & lngLast).AutoFilter Field:=27, Criteria1:="ASIA"
Range("A13:Z" & lngLast).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("ASIA DETAIL ").Select
Range("A19").Select
[code]...
I have the following code that gets stuck in the Loop and will not stop unless I press the Esc key:
Sub WIP()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lastrow As Long
Dim newRow As Long
Set ws1 = Sheets("PAYCALC")
Set ws2 = Sheets("WIP")
Application.ScreenUpdating = False
With ws2
.Range("A2:C" & .Range("A2:C2").End(xlDown).Row).Clear
End With
x = 10
lastrow = ws1.Range("C5").End(xlUp)
Do
newRow = ws2.Cells(65536, 1).End(xlUp).Offset(1, 0).Row
ws2.Cells(newRow, 1) = ws1.Cells(x, 2).Offset(-2, 0).Value
ws2.Cells(newRow, 2) = ws1.Cells(x, 2).Value
ws2.Cells(newRow, 3) = ws1.Cells(x, 2).Offset(3, 0).Value
x = x + 21
Loop Until x >= lastrow
There's a twist to this. I have the same identiclecode in a different module. The only difference is ws2 is different and there are 7 newRow's. Other then that the two are the same.
That one works just fine without getting stuck. It will go through 200+ sets of records and stop when it reaches the last one.
The one above will not stop even though I just put in three records on ws1 for testing.
how I can disable an InputBox? I've got some code that whenever someone selects a cell in a specified range, an input box pops up (running a macro) - this can get annoying sometimes though if just browsing. Does anyone know a macro where I can "disable" this?
View 4 Replies View RelatedI have a file that shows a rolling screen and updates information every 30 mins. I want to be able to stop the screensaver from activating while this file is open.
I have found this code on the forum that does this, how do I call this function from another module? ...
I have a VBA code that when activated it copies 6 cells from sheet1 to the next available row in sheet2 - A2:F:2.
alerting and stopping the routine if any one of the cells in sheet1 are empty so the user has to input into all cells before the routine will execute.
does it need a check cells for content if error highlight empty cell.
I have a simple macro that copies data from worksheet to another. I only want this macro to be run once per day by the user. One of the cells that gets copied is the value found in the formula "=today()". My thought is that the macro could look for the existance of the current date in the pasted data (meaning the macro has already been run once today), and if the date = today, then a message pops up warning the user that the macro has already been run once today, and ask if they're sure they want to continue.
Any easy way to accomplish what I'm attempting to do? The current date gets pasted into a worksheet name "PriorDay" in cell C5.
I need to stop users from adding and deleting sheets in a workbook
My idea was obviously to disable the command bars to add or delete sheets in open event and then put back in before close.
But then i thought...whats stopping them from right clicking the sheet tab and inserting a sheet, can i remove that menu to...?
I also wasnt sure if there were short cut keys to add or delete sheets.?
Some of my users use excel alot so i want to account for an tips they know that i might not.
I've got a worksheet_selectionchange macro on a sheet, and another macro that you can run after it. The issue is that when the second macro runs, it also runs the selectionchange macro, and wipes some of the info that the second macro should be copying.
Is there a piece of code that I can use in the second macro to block the selectionchance code from running until it's compelte?