If Not stfound Is Nothing Then 'if value is found then do this
is causing me a run time error 424 when I try and run my code "Object required". Entire code is pasted below...
Option Explicit ....
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 have error trapping in place so if a find in a column returns no data the procedure continues, but the code breaks there anyway.
In Tools/Options/General/Error Trapping I have Break on Unhandled Errors checked.
I have changed my error trapping lables.
I have copied the procedure and renamed it.
I need my program to:
- find the cell containing the string "Datum/Tid"
- record the column and the row of the found cell in two variables lCol and lRow
Here is my
Sub test()
Dim rFoundCell As Range
Dim lRow As Long
Dim lCol As Long
'Find method of VBA
Set rFoundCell = Range("A1")
Set rFoundCell = Worksheets("Sheet1").Range("A1:Z50").Find(What:="Datum/Tid", After:=rFoundCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
'for anyof the two lines down I get the message "object variable OR block variable not set"
lRow = rFoundCell.Row
lCol = rFoundCell.Column
End Sub
I have a spreadsheet form with all kind of values and what I want to do is to find a data in a worksheet named "Config" based on the spreadsheet activecell value. With the find row adress I return data of another column but same row (in the worksheet) in two diferent label captions. If the spreadsheet activecell value it was found in the worksheet everything is ok but if the value is not found I'm getting the message "Object variable or with block variable not set"....
View 7 Replies View RelatedI have 2 worksheets, one called Summary and the other called LNB. Column A in both worksheets contain account numbers.
I am trying to tell excell to look at the LNB worksheet and find the account number that is on the summary worksheet. If the account number is found, then tell me the row, on the Summary worksheet, that the account was found.
The function returns "Empty" even though I know the accounts do exist on both sheets and I dont know why.
Here is the code ....
I want the select case list of a ComboBox to be treated as a variable in order to shorten the code size. To clarify the problem, i post the code with what i want to do, but don't know how to do it that way.
Code:
Select Case ComboBox1.ListIndex
Case 0: y = "AT"
For j = 0 To 26
Case "j": y = sheets("name").Range("A(j)") 'Range A(j) is a string, so y as well, as seen in Case 0.
Next j
End Select
Is something like this possible?
trying to convert an excel document to an XML document and am getting the above error. Microsoft Visual Basic highlights below.
Code:
' prompt user to save to a directory
With frmAuthenticate.dlg
.Filter = "XML Files |*.xml|"
[Code]....
I have designed ad workbook with a lot of combobox. The cbo are partly controlling other cbo. The sheet works perfekt and in the code they are handled as intented. So no mispelling of any cbo or sheetname.
Where I get my problem:
If the workbook is open and I close Excel then I get the "Compiler error: Method or data member not found".
But if I close only the workbook I don't get any Compiler error.
It is as if the problem is caused by a Excel problem - If Excel is closed the sheet is recalculated but closed and there fore ends in compiler errors.
This error is in Excel 2000 + 2003 + 2007 - so not version related.
I have been searching the net for a solution for this problem but haven´t had any luck so fare. I starting to believe that there is no solution to this proboem.
This macro is part of the Pertmaster Risk Expert Application: It was not created by me nore modified by me, it is part of the PertMaster Risk Expert Software:
I am trying to run the Convert Lags To Tasks Macro Ver. 1.3 but i keep getting the following Error Message: Compile error: Method or Data member not found
Here is the list of the codes for the macro: I have not change any codes from the original pertmaster file....
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")
[code].....
Until now if I needed to find a cell's data (and its adjacent data) I've been using code in the format of (for the purpose of this example, you can assume all data in coulmn 1 is unique. Functioning as aprimary key using incremeting integers):
View 3 Replies View RelatedI have found many posts similar to what I am trying to accomplish, but nothing that I have been able to modify and make work. I need a VBA script that will find a cell with the text data "Difference" and copy the adjacent (to the right) cell's data. I then need to find a cell with text data "Ops" and paste the previous data to it's adjacent cell.
View 3 Replies View RelatedWhen I use VLOOKUP formula within a VBA routine ex:
For LoopCounter = 1 to LastRow
With Worksheets("0MATERIAL")
.Cells(RowCounter, 5).FormulaR1C1 = "VLOOKUP(RC[-2],'0MAT_UNIT'!R1C1:R65520C12,4,FALSE)"
End With
RowCounter = RowCounter + 1
Next LoopCounter
How can I find out what row or cell address that the VLOOKUP found the data? The above code works perfectly but the problem is I need data from that same row in columns 4-11. I replicated the .Cells line above and made each one a VLOOKUP (for column 4, 5, 6, etc...) but this kills the performance. I have around 32,000 rows in sheet 0MATERIAL and around 50,000 rows within sheet 0MAT_UNIT
I want to find the subtotal amount on one worksheet, which is two columns over from the text, "Sub Total:" on the same row. The subtotal line floats up and down each week. Once I can find the subtotal I want to populate another worksheet with the data.
View 5 Replies View RelatedLooking to find 1 of 2 words in a cell in column B and return the word found in the same row in column E. This seemed easy but I am not having any luck.
the cells in column B have several words in them but I am looking for 2 specific words "PLAT" and "ORIG". If the word is not in the cell, it should show a blank cell in column E in the same row, otherwise one of the 2 words should be in that row in column E. A VBA loop would be ideal but a formula that can do it might work as well.
I have a file that the user selects and when they enter a value (in this case, a job title) into the input box, my macro looks for the value in that file. If the value is there, a msgbox pops up that lets the user know that the value was found and it then goes to that cell, displaying in the next msgbox the cell address of where the value was found. I already have the code down for this part of the macro.
My problem is what happens if the file has the same value more than once. Ideally, I'd like to display a message box that returns the addresses of both cells with the same value and then prompts the user to select one of these values as the value they are looking for. I am not sure if a msgbox or a msgbox and then an input box are most appropriate for this situation. Once the user does this, the macro continues. The rest of my macro is built on the cell where this value is, so it is crucial that I make sure there is at least one value selected. Any help is much appreciated. I have included a sample worksheet of what this situation might look like.
Here is the code I presently have for this part of my macro.
Sub GetOpenFileName()
strFind = InputBox("Please enter the job title you wish to search for:", "Search for job title in this file")
If strFind = vbNullString Then Exit Sub
With Sheets(strSheetsMainCompProfile)
If WorksheetFunction. CountIf(Range(Cells(1, 1), Cells(100, 100)), "*" & strFind & "*") = 0 Then
MsgBox strFind & " cannot be found on this sheet"
Else
I am trying to clean up and simplify my macros by compartmentalizing frequently repeated commands. I like to search for text in column headings and define the cell containing it as a range that I can later use .column or .row commands on, so my macros are a bit more flexible. The code I currently use for this is
Dim XColumn As Range ‘the cell containing the text “X”
Cells.Select
Selection.Find(What:="X", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Set XColumn = ActiveCell
I would like to be able to create a Macro (possibly name FindSetCell()) that will accept arguments and achieve the results of the above code in other macros
I think the command might look something like FindSetCell(“text to be searched for”,”name of newly Defined Range”) But I have not figured out how to code it
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 trying to sum the cells in a column from a variable starting row to a variable ending row. My problem is that I can not figure out how to use the "Range" method with variable values.
Below is what my code would look like if I was summing from I2 to I6 (hard coded)
If Sheet6. Range("O8").Value = 4 Then
For Each cell In Sheet10.Range("B5", "B369")
If (cell.Value = Sheet6.Range("L24").Value) Then
cell.Offset(0, Sheet6.Range("L21").Value).Value = Sheet1.ActiveCell.Value
End If
Next cell
End If
it says the part of code causing the error is the end of the 'cell.offset' part, as highlighed: Sheet1.ActiveCell.Value. i tried replacing it with '.Selection', still get the same error.
I have the following
View 2 Replies View RelatedLet CopyRange = 'A' & Roll
Range(CopyRange).Select
Roll is dimensioned as Long, and is a counter for line numbers. Is this legal? I'm getting:
Method 'range' of object '_Global' failed.
I'm getting a compile error whenever It gets to the .leftmargin line...its the "method or data member not found" compile error
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
When I comment the margin stuff out, everything else is forrmated correctly, the landscape and all. It really doesn't that "application to inches."
After doing a search, I need to ask an Excel 2003 question. I'm creating a tool to open three sparate workbooks. One is the driver and I need to use it to pull data from the second and then to place that data along with additional data into the third.
My driver data may consist of a single value or multiple values separated by semi-colons in a cell. A single value works fine. The first value in a multi-value condition works fine. the second find, however, gives me an object error. I'm using the " split" verb to separate the values. Here's my code. Have you any idea why the second find is throwing up this error when the first find works correctly?
varData = Split(strRef, ";", -1)
For J = 0 To UBound(varData)
varSrchVlu = Trim(varData(J))
' do the ARIS Exrtact matching
Workbooks(strManualFile).Activate
Workbooks(strARISExtract).Activate ' Activate ARIS Extract
Sheets("Processes").Cells(2, 1).Activate
Workbooks(strARISExtract).Sheets("Processes").Range("A2").Select
Workbooks(strARISExtract).Sheets("Processes").Columns("A:A").Select
I'm looking to identify a keyword in a string and then replace that string with just the keyword.
The string is a product description. The keyword is a product group. The keyword can showup in any position in the string.
I can't post the actual data do to confidentiality requirements. but here's an example.
description
qty
price
keywords
code
green grapes bunch
1
2.5
[Code] .......
Need to find the first cell above the active cell containing any sort of variable (integer, string value, cell fill colour, named range, etc...).
For example, a column contains multiple integer values between 1 and 10. I would like the macro to "locate" a specified value, say 3. Ideally the macro will select the first cell matching this criteria, located above the active cell. Once located, the resulting cell will be added to a range to be copy-pasted to a new worksheet.
I am planning to use this code for various applications in a workbook I am creating. At present the code will be used to locate string values, and cell fill colours. It is also likely this will extend to other types of variables in the future.
I've found information relating to the find function, but nothing specifically to find the first result above the active cell.
I'm trying to work on a macro that will copy two cell ranges (name of current month and prvious month) from one location, store it as a variable and use that variable to find replace similar text string in another tab. Below is my code which is not changing the values in another tab...
Dim OldMonth As String
Dim NewMonth As String
Sheets("X1").Select
[Code]....
I am having trouble using the find function. I need to store a date as a variable and then find this date on another worksheet. The date is in the following format:
dd-mmm-yy
This is what I currently have which gives me a run time error 91:
Code:
Dim DateSearch As Date
DateSearch = Range("C3").Value
Cells.Find(What:=DateSearch, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
I am still a novice in you standards, but have run into a problem and hoping you all will have some insight for me.
I have a workbook that I am parsing data from the first sheet and putting the data onto the second sheet. Before I place the values in I want to format the cells with boarders, colors, alignment, and in some cases validation (but I can worry about the validation later).
Currently what I am doing is using a 3rd page that has a group of cells that have the formats I want in place before I start. The code will then parse the data and when it finds the data I need go to sheet 3 and grab a range of cells and paste those cells to sheet 2 and then write the data over those cells. This allows me to format the document as I create it.
The problem is that I am going through 25k of lines and on average this function is running 40+ minutes. If I run the same script with out the formatting it only takes at most 2 minutes.
So on to the question.
Is there a way that I can start of my script by grabbing all the format information before I parse the data and save it as a variable? Then instead of doing a copy of the format it would just apply the formatting that is saved already. I would think this would be much faster of a process.