I have an Excel workbook that i need to loop through all rows searching for a certain string we can call "Totals". There are more than one, and i need to grab the values from each at an offset in the same row, different column, to total at the bottom. I have tried a few things unsuccessfully. The code below is just my first step to try to find the row, i know there are problems as i am unfamiliar with the different properties of a given variable.
Function FindRow(szName) As Integer
nFoundRow = 0
For nRow = 0 To Worksheets("Totals").Columns(nCol).Rows.Count
If StrComp(Worksheets("Totals").Cells(1, nRow), szName) = 0 Then
nFoundRow = nRow
End If
Next nRow
FindRow = nFoundRow
End Function
I'm looking for some code to loop through some rows of text and then if the text contains / replace that with a space. My code is below:
Code: do until intdemandrow = 1 If InStr(Range(cells(intdemandrow, 1).Value, "/") Then replace(cells(intdemandrow, 1).value, "/", " ") intdemandrow = intdemandrow - 1 end if loop
I am trying to Find a cell containing a string ("derf" in this case). Then bold the entire row and continue to loop till it hits the end of the row and column. Here is what I have.
Sub Macro3() Range("B65536").End(xlUp).Select 'Finds the bottom in column B and sets it to endhere ActiveCell.Value = "endhere" Range("A1").Select 'goto the top Do Until ActiveCell.Value = "endhere" 'The Do Loop Starts Rows("1:30").Select Set rfoundcell = Selection.Find(What:="derf", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) With rfoundcell rfoundcell.EntireRow.Font.Bold = True End With Loop 'Keep looping till it hits the bottom of the columns row! End Sub
In the code below I find rows containing "$$ #" at the far left and process it. For all the remaining rows I want to delete the entire row, my macro only deletes some rows. Obviously I am not grasping something about looping throught the rows to delete unwanted data.
Name Address Phone # zip Danielle 4561 Danielle 9852 Danielle 22 Danielle 69 Joe 895 Joe 28 John 9821 John 1114 John 698
Say I did a search for Joe. I want to report back all the addresses in which he resided but there's no way to tell how many rows of data each person has. Joe has 2 rows, Danielle has 4 rows and John has three. How do I report back all the relevant rows?
I have working code that returns a row number within a for loop based on parameters I set.
Each time the for loop runs I would like to store this row number, then after the loop has finished, delete all stored rows.
Code: for rowNum = 1 to x (some variable end row number which I already have worked out using End(xlUp).Row) if x = y then *storedRow = rowNum end if next rowNum *
Lines with a * are the bits I can't work out. I've been trying to understand arrays by reading posts on what other people have done, but I can't fit (or fully understand) the reDims, or reDim preserves into my code. I've seen what appear to be quite complex ways involving uBounds and LBounds, but unfortunately I can't see how to use them.
All I want is to simply keep adding a row numbers to a variable, (i.e. row 2, 5, 20, 33, 120, etc) and then delete those specific 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 am having trouble getting my IF statement to test if the cell contains the text "sale" return "X" if not "Y". I need it to search through the text string in that cell and find a certain word, and if it finds that word, retrn a value. I am really having difficulty with is what symbol or function do I use for the logical test? (i.e. =, <>, MATCH, INDEX?)
I have some code that I use to convert files, and it works well, but I would like to make a couple of changes to it. If I run it now it requires user input for every file that is processed, using the input box. Instead of this I would like to use the filename minus the extension as this input.
The next step is selecting the file using the . Instead of this I would like the user to browse for the folder containing .csv files, and select the folder, then use the macro to loop through all files in the folder.
I am trying to do a sort of index match thing using VBA. What I am attempting to do is to use the prefix of a long number and try to find that exact prefix in a string array, and output that string array value. So this is what I have so far as a test:
[Code].....
So I can match the text exactly so if I put PREFIXB in cell A1 in this example, i will get the msg box saying "YES", but if I make it PREFIXB1231k4j3jj1kj32cj, it will display "NO". I want to get it so that PREFIXB will be displayed in the cell that I put the formula in. So if A1 = "PREFIX1AAA100CF" and cell B1 = "=ABC(A1)", cell B1 will display "PREFIX1AAA".
Now the thing is that these prefixes can have different lengths, but will never encompass the exact prefix of another. So if I had a prefix of: PRE1AB, I won't have a prefix of PRE1A.
i have a data sheet and i am trying to store the row value of a user input time. for the most part my code works but for certain times it returns a 0. it seems like times which end with a 3 are not returning any value.
my code looks kind of like this:
Dim StartA As Date StartA = InputBox("First start time (ex: 2:30pm)") For i = 1 To 9000
i have a piece of code which i have cobbles together from many different sources, to many to list, but i know I have had some help from some of the people in this forum.
first of, i know some of the code might be a bit shaky, but please bear in mind that i am very, very new to VBA.
basically the code is reading the first usable line of the "Mapping" sheet to get information, then it searches for that information in the "GLEX" sheet. If it finds the information that was searched for in the "GLEX" sheet, it will copy it to the "Calc" sheet. the "Calc" sheet contains a formula that does a sum on everything in the P column. It then takes the value in the P column on the Calc sheet and pasts it into a cell that it reads from the "Mapping" sheet.
If i run the code, it seems to be doing what I want it to do for the first line in the mapping sheet, but how do i make it continue?
If must read the value of the first usable line in the "Mapping" sheet, then search for those values in the GLEX sheet, if it find a match it must copy the whole line to a next sheet and then CONTINUE SEARCHING. the mapping sheet. Once it has search the whole GLEX sheet and copied all the matches to the calc sheet, it must perform the rest of the functions. after everything has been completed for the first line in the Mapping sheet, it must do the same for the second line.
That means that there must be a loop in a loop.
I have also included some sample data.
Sub Accrule() Sheets("Mapping").Select Dim Org_Area As Variant Org_Area = Worksheets("Mapping").Range("A3").Value Dim Item As Variant Item = Worksheets("Mapping").Range("B3").Value
I am using Range. Find() and Range.FindNext() Method. As per my knowledge FindNext() advances in the text and searches for same string found using Find(). I have used Find() for two different searches in Sheet. (Eg. Find() text "Risk" and Find text "ACL", Now when I use FindNext() for the second time (to search "ACL" text), next time when I use FindNext() to search first text ("Risk" it searches prev text - "ACL"). I have used them in a loop. Below is the code i have used.
Set SrcCell = SourceBook.Worksheets(1).Columns("A").Find("Risk", After:=Sheets("SubArea").Range("A21"), LookIn:=xlValues, lookat:=xlWhole) 'Set srcCell = SourceBook.Worksheets(1).Columns("A").FindNext Set firstSrcCell = SrcCell Do While Not (SrcCell Is Nothing) tgtCell.Offset(tgtRow, 0) = SrcCell.Offset(-1, 255) tgtCell.Offset(tgtRow, 1) = subAreaId tgtCell.Offset(tgtRow, 2) = SrcCell.Offset(0, 1) tgtCell.Offset(tgtRow, 3) = SrcCell.Offset(1, 1) tgtCell.Offset(tgtRow, 5) = SrcCell.Offset(3, 1) tgtCell.Offset(tgtRow, 6) = SrcCell.Offset(2, 1) RiskId = SrcCell.Offset(-1, 255) 'tgtCell.Offset(tgtRow, 0) = SrcCell.Offset(0, 255)...............
I have a sheet with data on it. In column 5 I have adate range and I would like to delete and values which have "01/01/1900". Currently I have created a loop but for some reason it does nothing. I can use the find option and it picks up the cells with the ones on. For some reason it seems that it cant find them cells.
The code is as follows
Sub jdate()
rowcn = 8
target_sheet = "Status Report" Do 'status_flag = 0
If Sheets(target_sheet).Cells(rowcn, 5) = "01/01/1900" Then Sheets(target_sheet).Cells(rowcn, 5) = "" End If rowcn = rowcn + 1 Loop While Sheets(target_sheet).Cells(rowcn, 1) > 0 End Sub
I have a range (C3:C56). I have a set number in each of these cells. I also have a range out to the side that calculates a number after input a % in cell BI2. The formula reads as "=AE3-(AE3*$BI$2)". Basically it is reducing the values in my range. If a number is in my range is 8 and i input 25% in cell BI2, then my result would be 6.
I have a few steps I want to happen with this range.
1 Display an input box asking for a percentage 2 I want to loop through my range and use that percentage to reduce the numbers 3 (optional but would be amazing to have) after the reduced value is determined, round down to the nearest multiple of 4.
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 am putting in search items and running a macro to find the items on 'physical servers' WS, copying the header in that WS and the entire line the match appears on, though I cannot get it to do this.... it is really causing me stress
Then next part that is working is the items that return false are showing up on the results page - this is expected and what i want it to continue to do.
What I cant seem to work out also is how to run the search on the 'Virtual Server' WS also and return the results to the results WS as just like the 'physical servers' WS.
I have included some dummy data + code + the expected result on the 'Server Results' WS.
I was tasked with looking through a 10,000 row by 20 column spreadsheet for 628 different ID Codes and replacing them with their Descriptions. The ID Codes could appear individually in any of these cells (200,000 cells!).
The list of ID's and Descriptions hardly ever changes, so I decided to create the two-dimensional array as part of the find-replace macro shown below (only a few lines of each dimension are shown).
My question isn't related to that (although if you can suggest a better alternative please do). The main "work" of the macro is the loop at the end of the array declarations, which essentially loops through all 628 ID codes in array dimension 1 and does a Find-ReplaceAll with its corresponding Description from array dimesion 2.
This task, manually, could take days. My macro has whittled the task down to 2.5 minutes on a 5-year-old laptop, but I was hoping one of the gurus might suggest an even better method than 628 loop iterations. If not, so be it, the end users will appreciate what I've done and then have to find something to do with their "free time."
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.
make and loop that loops true all values in column A and B. For example, if the cell A3 is higher the cell B3 then I want it to write the higher value in new sheet.
I need to loop through a worksheet to find the following product ID's: 100805, 6950000, 853000 and 20994000. Each time I find the product ID I have to execute the same code in that part of the worksheet to extract data.
I just do not know how to set up the macro to loop through each ID.
The following bit of code has worked for me but when it goes back for the next b it still finds the address as $j$13. This address does match the criteria I want but what I want the programme to do is move to the next address matching the criteria in the range...
Attempting to hide columns (of cities) via VBA generates an error when that same city is reselected (either individually, or as part of the group) in the list box, upon clicking the 'Hide' button.
