With Loop + Find Method And Sum The Negative Values
Feb 23, 2010
I have a very large worksheet (row count maxed in 2007, and then some), for which I need to do the following: search column A for a string that will occur many times, and then check the 10 cells that follow in its row for negative values, dropping some sort of indicator in the 11th (shading it red or something would be fine). An additional bonus would be if the 10 cells that possibly contain a negative could be summed (the sum could serve as the indicator?). If no negative is found, nothing need be done, and the macro should chug along searching A for the next reference to this string.
My hope was to do a sort of "With Range("A:A"), .Find("MyString")", save position as StartPos, do the 10-cell row checking in a nested IF or For (though the For would take a long while, checking each cell individually), then doing a .FindNext after StartPos until = StartPos (does .FindNext loop back to the top?). The formatting of the indicator cell in the 12th cell in each relevant row doesn't really matter, it's more just for jumping to critical rows.
I'm trying to use this Find Method and combine it with a countif or loop. Something that will count a number of occurences of a unique type of character. I'm looking to find all "F" characters in Bold, Italic and Size 16. Here's my find code that I'm trying to use. I can get it to work by itself but not along with a countif or loop.
Sub count_4() Dim r As Range Set r = Range("A1:A6") With Application.FindFormat.Font .Bold = True .Italic = True .Size = 16 End With r.Find(What:="F", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, searchformat:=True, MatchCase:=True).Activate End Sub
I have data in range J2:J365 , H368:H401 & J403:J827. i want to check wether this range have negative values or not if yes load all negative values in the listbox1 by clicking checkbox.
I am trying to find a formula where I can identify where in row two the value chages from positive to negative, and where it changes from positive to negative it should return the date the value changes from positive to negative ie:
My worksheet contains several codes that I then have to lookup in a different worksheet in a different workbook to obtain the relevant data associated with that code. At present I do this by activating the appropriate worksheet, Using Find to locate the appropriate field in that worksheet and then using activecell.offset to get the needed data. This is the only time that these worksheets ever need to be activated so I was wondering if there is a way to do this without having to actually activate the sheet. As an exapmle this is what I currently do:
' variables defined earlier in the program, Tempsheet = workbooks(FileNm & ".xls").worksheet("Temp")
Spark = Cells(TRow, 8).Value ' Activate the reference Sheet Workbooks("Waveguide Properties.xls").Worksheets("Spark Bends").Activate Cells.Find(What:=Spark).Activate COM = ActiveCell.Offset(0, 2).Value Edge = ActiveCell.Offset(0, 3).Value SparkArray(i, 1) = ActiveCell.Offset(0, 1).Value SparkArray(i, 5) = ActiveCell.Offset(0, 4).Value SparkArray(i, 6) = Spark ' Reactiave the workbook and worksheet I am working in TempSheet.Activate
I'm working with reports where I am given a list of used RFID tags which contain 13 alpha-numeric characters, and need to compare it against another list, in order to determine if any are matching. I'd like to be able to loop through one column of values to compare against the other, but unfortunately my VBA skills aren't that great..
I have a range of amounts in Sheet 1 from F7:Q13 and im using the find method to search for the active and non active values in the cell. Which means that if there's a value in the cell it will transfer the value in Sheet 2, if nothing is found in the cell the cells in Sheet 2 will return as nothing or null.
I think the problem lies on the FindWhat variable. Im getting a compiled error which im not sure what is it.
I've attached the spreadsheet so you get a better idea of the problem that i encountered.
In this database i have cost and sale price. 20,000 parts have the numbers backwards.
if you loop the rows you can subtract =SUM(AE2-Y2) if that number is a negative number I would need the value of AE copied to Y2 and the value of Y2 copied to AE2. the 2 being the row number which would always change with the loop.
This line fails, is Method Range Global error.... What is wrong w/ this, and how should it read?
If Range("D", i).Value = "Need Parent" Then 2nd Question- The following line is my normal method in doing a simple For Next Loop. What is the difference between using this one, looping through the cells and the first one referring to a Range for the loop?
I have an excel sheet being used as a mini database table.Rows = records, columns = fields. I have some VBA to create a copy of base template in the workbook, then populate the new template with the data from a row/record in the db. I currently have about 100 records. After about the 57th record I recieve RT error 1004. "Copy method of worksheet class failed". I think this is becuase excel is running out of memory. My laptop has 1gig of ram, and i have closed all other apps when running the macro.
Is there a way to free up memory while the vba is running, without clearing my "for" or count position which tells the macro to create a new sheet and which row/record in the db to populate the data in the new sheet.
In my workbook I have about 20 sheets (less in the attached sample), and on most sheets I’d like to query data for each day and find an average value based on the time and then copy that result into another sheet. I’m not sure if I’ll need 2 macro’s for this or if one can be used.
Here’s a quick description:
Assuming that I am currently in the sheet I want to run the macro, then I will either input “1:45” or “0:45” into a popup box, or I could always simply input the 1:45 or 0:45 in the code itself. I’m flexible
1:45:
If I input 1:45, then I’d like the macro to find the first 1:45 in Column B and the value in the same row in Column F will be used in the calculation. Once 1:45 is located, then the macro moves up to find 22:45 in Column B and the value in the same row in Column F will be used in the calculation.....
I'm trying to rank the values in cells S32:S38 in ascending order while ignoring zeros and negative values. I also need to rank duplicate values with a unique ranking, so that no ranking value is repeated. I tried the formula below, but the ranking values start at "2" instead of "1" and I can't figure out how to fix it.
is it possible to write a macro that will convert absolute values into negative based on criteria? Data gets downloaded from in house system which comes out as all absolute values, now i need the macro to covert absolute values to negative if the amount is either "Our Reciepts" or "Our Delivery"
Below is a small sample of data, real data is around 100 rows
Col DCol EOur Receipts1231Our Delivery1231Delivery Settlement1323
I am trying to show how many years it will take for a retiree to run out of money.
row 1 is his available money (this in determined with other formulas such as income - expenses ect)
Row 2 is number of years (J2 would be 10 years)
Let's say available money turns negative on the 10th year (tenth column "J")
How can I write a condition statement that will say that says if the amount in row one is positive do nothing, but when it turns negative add row 2 of whatever column it turned negative in?
Example:
A B C D E F G H I J K L 5000442538503275270021251550975400-175-750-1325 123456789101112
Private Sub cmdShowdata_Click() Dim Tgt As Worksheet Dim Source As Range Dim wbSource As Workbook Dim cel As Range Dim rng As Range Dim c As Range Dim i As Long Application. ScreenUpdating = False Set Tgt = ActiveSheet Set wbSource = Workbooks.Open("C:Documents and SettingsDesktopStaff Recoed 2") Set Source = wbSource.Sheets(1).Columns(1) With Tgt .Activate 'clear old data Range(.Cells(3, 2), .Cells(200, 5)).ClearContents ' Loop through names in column A For Each cel In Range(.Cells(3, 1), .Cells(Rows.Count, 1).End(xlUp)) If Not cel = "" Then...................
The above vba command which is extract the data from the Other workbooks. It looks for the "Staff 001", "Staff 002"...these parameters to transfer the data to the worksheet. But, the "Staff 001" data must appear twice in each workbooks. If i use the above command, i only can extract the FIRST "Staff 001" average data. But SECOND "Staff 001" average data cannot extract. I know it may be use FindNext method to do this but i am not sure how to write it
With Sheets("regrade pharm_standalone") For Each r In .Range("standaloneTerritory") If r.Value = "X101" Then r.EntireRow.Copy Sheets("X101").Range("A1").End(xlDown).Offset(1).PasteSpecial xlPasteValues End If Next r End With ------------------- I need to repeat this loop for values from X101 to X151. In all cases, the sheet name is equal to the value I'm looking up (eg: value = X102 goes to sheet X102).
I have a named range called 'territories' that contains the list of X101 -> X152.
I'm hoping to make the code perform the loop for each of the territories without my having to copy & paste and change the 'X101' 51 times as this would seem a rather silly thing to do!
Sheets("Tracker").Activate Set StudentTbl = Sheets("Tracker").Range("A1:" & "Z" & NumStudents)
Set NamePosition = StudentTbl.Find(What:=RightName, LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) I thought the find method returned a range?
I am having trouble using teh find method. I'm using it to search for a string in a column, then give me the row number, which is fine when the string is found, but when it is not I get an error of "variable not set." I'm pretty certain it is returning void, but how to I capture that? Here's the
The objective of this macro is to sort through a database and find all entries that match up (ie 12345 and -12345) and delete these records. when i run it foundCell always returns nothing.
Sub recSheet() Dim balance As Double Dim balanceCell As Double Dim BalanceVal As Variant Dim FoundVal As Double Dim calcmode As Long Dim ViewMode As Long Dim FoundCell As Object Dim myRng As Range Dim sh As Worksheet
With Application calcmode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With Set sh = ActiveSheet Set myRng = sh.Range("I:I") With sh .Select .................................
giving me a macro to find a word in a sentence by using the "FIND" method. For example I have a sentence say "I am happy" in which I have to search whether any of 2 words say "Happy" or "Sad" is there or not. If the sentence contains any of these words, then that row has to be highlighted.
Using Excel 97, Is there a way to Sum the values in columns if the value in cell B is a negative number. I want the total placed in their respected columns after excel finds 8 Consecutive Blank rows.
Ex
B1:B70 (remember the number can change it can also be B1:B80)
B1:B70 is a positive value, but then B85 is negative so begin the calculation from that point.
Sum B85,F85,G85,H85,I85,J85,K85,L85,M85,Q85,R85. And place the value in their corresponding cell after 8 consecutive blank rows appear in row B.
How to get the GetFromClipboard syntax from here working with my code (below), but am having a really frustrating time. Basically, I need to search for whatever value is copied to the clipboard (as you can see below which will be relative to a certain cell).
Here's what I have so far, and I'm trying to replace the contents of the What:="121212121" part of the Find method to look for the clipboard contents (a 9 char number) in another sheet (where a duplicate exists - the value I am searching will be a unique identifier for the incorrectly created duplicate record that needs to be deleted).
So, What:=<insert number/string on clipboard here>
I am trying to use the .Find method to match a date. In sheet2 I have a range of cells which are formatted to only show the day "dd" (I could format each cell to show (dd-mmm-yy, etc). In sheet1, I have dates which are formatted as "January 7, 2009"
I want to create a VBA find routine which allows me to match the date from Sheet2 (Ranged between cells A1:Z1) with Sheet1. Then output a MsgBox stating "Match found in cell $A$7 of Sheet1" I have tried a formula similar to:
PHP Set FoundCell = Sheets("Sheet1").Range("A1:Z1").Find _ (what:=DateValue("January 7, 2009") ,lookin:=xlFormulas)
My .find function is only finding the 2nd value in a cell example If i look up the term " HOSE"
5/8 hose water Will return 5/8 heater hose will not
Basically what I am making is something I am calling a "what" button. It queries my database and returns all the part numbers with the term i put in and i thought it was working fine till i was testing it, did i do something to select this? I've tried xlpart and xl whole. Neither is working.
Here is the code below
Sub COPYPARTNUMBERINFO()Dim parttofind As String Dim partcell As Range Dim partref As Range ActiveSheet.Unprotect Password:="*******" Range("t6:T10000:u6:U10000").Select Selection.ClearContents parttofind = InputBox("What is the number or discription")
