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
[Code]....
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 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 have a process where I need to search for multiple customer numbers and delete line associated with them.
My question is how do I create this process to run in a loop going through all 10 numbers?
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 thought my code was working properly, but then I tried different numbers for myNum and I realize it's not. Here's the
View 5 Replies View RelatedI 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..
View 5 Replies View RelatedI 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
Sub Totals()
nCol = FindRow("Totals")
ActiveCell.Select = nCol
MsgBox (nCol.Value)
End Sub
I've an excelsheet with quite advanced formulas that area really nested and using quite a bit of iteration.
What I would like to do is finding the maximum of
A while Amin<A<Amax
By changing B
There is a Variable C than should be constant unless it becomes larger than Cmax ( Cmax dependas on A,B ,C, D and all kinds of things)
If C is > than Cmax there is a variable D that can be changed has to be less or equal to Dmax
So the question is how to find the maximum of A while fulfilling all these variables?
What I've come up with so far is:
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.
Hope you all can work out a way to make it work.
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.
View 2 Replies View RelatedI'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 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...
View 4 Replies View RelatedI want to search a column, from A30 moving up to A10 for the first value in excess of 0.
Having found the value the search stops and I want to select the cell next to it in B column.
Then I want to check if a value in B column exists. If no value exists I want to enter a fixed value from another sheet.
If a value does exist I want to take no action and move on to Column C to repeat the checking process for columns C and D.
Then I want to move to the next spreadsheet and repeat the process.
The code I have written so far is below and I just can't get it to work.
I have attached a spreadsheet to illustrate the problem.
Sub Closingdata()
Dim a As Integer
Dim b As Integer
Dim rngOutput As Range
Dim shtTemp As Worksheet
Dim vntName As Variant
For Each vntName In Array("sheet1", "sheet2")
Set shtTemp = Worksheets(vntName)
shtTemp.Select
shtTemp.Range("a30").Select
Do Until ActiveCell.Value > 0
If ActiveCell.Value > 0 Then
ActiveCell.Select
Exit Do
End If
I am trying to write a macro that will find duplicate listings in two worksheets by row. I don't really want to do a user form unless I have to.
View 9 Replies View RelatedI 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
I have 12 Workbooks (each for every month) name Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
These workbooks contains something like the following
Name
Days of Vacation
Something Else#1
Something else#2
Something else #3
[Code] .....
I want something to loop through the months and copy all rows for mary in a summary sheets and sum the Days of Vacation from Column B.
Note that The first Workbook has some data, the 2nd Workbook is the Jan and the 13 Workbook is the Dec, the 14t is the Summary Workbook
VB:
Sub SearchForString()
Application.Calculation = xlCalculationManual
Dim LSearchRow As Integer
Dim LCopyToRow As Integer
LCopyToRow = 2
[Code] .....
I'm trying to create a macro the saves a report and then opens an email dialogue box populating the recipients from a list. I have the following code so far but the receivers are constant.
[Code] .....
I have attached a simplified example of the list. The list is always sorted by time and date. Not including headers, the list can be from 1 to an unspecified number of users long and users may repeat throughout the list any number of times.
The user name always appears as their email address prefix and the email address will always end in "@example.com".
I am aware that a loop could perform this task but do not know how to employ it in this case as it must not repeat recipients and will also need to populate the EmTo line. Possibly a second loop for this?
Attached File : Report Test.xlsx
I have a data sheet with employee information. Only one column. It prefixes information with codes, but keeps it in the same cell. I want to move data of certain types into their own columns, but the amount of data is variable, so I cannot simply move every Nth cell, etc.
i.e.
Employee#1
200 Firstname Lastname
204 99999999 (Employee ID)
G38 00005000 (i.e. Pension Deduction)
H38 00007580 (i.e. Benefits Deduction)
X96 00012099 (i.e. Staff Club Deduction)
Employee#2
200 Firstname Lastname
204 99999998
G38 00000775
X96 00001000
So you see some employees may have different codes altogether. But I know that I want all the cells that start with 200 to be offset (-1,1), and all the cells that start with 204 to be offset (-1,2), and so on so that basically I end up with columns of info instead of a one column list.
I have been reading and studying other peoples' macros, and am just starting to grasp the basic. When I wrote my own to accomplish this, I put this together, which doesn't work. But I don't know enough to know what I don't know.
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.
Code that loops through all folders in a directory, to check each workbook in each of those folders. If any workbook name in any of these folders matches a predefined string, then open this workbook for some other codes to run.
View 2 Replies View RelatedI’m getting data from query. My data has fields: Item No, Customer ID & Sales rep.
I need to find out for each Item number which customer put order and who was the sales rep. (Excel 2002). see file attached.
I have this code attached to a button on the first sheet of a workbook with hundreds of sheets.
it is suposed to look for a cell that contains "SAY:" and then move one column to the right and make it a zero. It works on the first sheet but not on any other sheet.
I am trying to loop through column A and I want to store in an array where I find "App" within the cell value. I am trying to find "App" but will store the whole cell value in the array. I could not figure out the Find method, so I tried the MID function but am having no luck.
Here is my code:
Code:
Sub Arraytest()
Dim arr As Variant, lastrow As Long, i As Long, f As Long, l As Long
f = 0
lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
ReDim arr(1 To 1, 1 To lastrow)
[Code] .....
I am trying to build a macro that will search a column for the word "TEST" and when it finds that word it needs to prompt the user with a YesNo message box (or something similar) if they would like to delete the entire row that contains TEST. If Yes is selected, it should delete the row and continue on to find the next TEST in the column and repeat this process. If the user selects No, it should continue on to find the next TEST in the column and repeat this process.
View 4 Replies View Related