This code is part of a routine that copies and pastes from CASH RECEIPTS sheet to YEARLY TOTALS sheet in the same workbook. rngCash is assigned as a Range variable.
In the various fixes I’ve tried, the code worked to varying degrees.
I'm fairly new to macro's and VBA, by searching on the internet i've copied and pasted some code together into a macro.
But it ends in a Run-time error 91...
The macro opens a target .xls file in a selected folder, performs copy - paste actions from masterfile to targetfile.
Than it filters data in the targetfile sheet1 and copy's the results to the various other sheets; saves and closes the targetfile.
The next target file in the folder is opened and the actions are repeated in this second target file.
For the first target file this works smoothly; but for the second one (of a total of around 100) it does not copy the filter results to the other sheets in this workbook.
The error message i get is: "Run-time error 91:Object variable or with block variable not set."
When i hit debug it highlights the line "ActiveSheet.Next.Select" which, at least in the first file, seems ok.
I Wrote a code which as intended to open each excel file in a folder and copy the data containing in it into a new sheet.
But While running the code the first excel file gets open, and an error message "Run Time Error 91-Object Variable Or With block Variable not set Error"
How to set the file which got opened from the folder to wbk variable.
Code:
Sub dataintoonesheet()
Dim i As Integer
Dim jk As Integer
Dim j As Integer
Dim rowstart As Integer
rowstart = 3
[Code] .......
When i try to run the code below i get the error message - object variable or with block variable not set-
Sub REFRESHXX()
'LIST
Cells(Sheets("POINTS").Range("DD801").Value, Sheets("POINTS").Range("DD800").Value).Select
Selection.AutoFilter Field:=1, Criteria1:="1"
'SET RANGE
Dim sFormula1 As String
Dim sFormula2 As String
Dim sCell1 As String
Dim sCell2 As String
Dim sSheet1 As String
Dim sSheet2 As String
Dim r As Range
Dim MyRange As Range 'for testing
With Sheets("Points")
sFormula1 = .Range("CY1").Formula
sFormula2 = .Range("CY2").Formula
End With
'FORMULA IN R1C1 STYLE
strFormula = "=IF(ISNA(VLOOKUP(RC[-1],MASTER!R4C3:R17908C7,3,FALSE)),0,VLOOKUP(RC[-1],MASTER!R4C3:R17908C7,3,FALSE))"
'ENTER FORMULA IN ALL CELL RANGES
r.FormulaR1C1 = strFormula
'REDUCE TO VALUES
Dim ar As Range 'an area is a range
For Each ar In r.Areas 'areas are discrete, contiguous ranges of cells
ar.Value = ar.Value
Next ar
'UNLIST
Cells(Sheets("POINTS").Range("DD801").Value, Sheets("POINTS").Range("DD800").Value).Select
Selection.AutoFilter Field:=1
End Sub
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 found this nice little bit of code for a date range search in column A but it will not work. Apparently i have not set a variable or something.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim startDate As String
Dim stopDate As String
Dim startRow As Integer
Dim stopRow As Integer
startDate = InputBox("Enter the Start Date: (dd/mm/yyyy)")
If startDate = "" Then End
stopDate = InputBox("Enter the Stop Date: (dd/mm/yyyy)")
If stopDate = "" Then End
startDate = Format(startDate, "dd/mm/yyyy")
stopDate = Format(stopDate, "dd/mm/yyyy")
startRow = Worksheets("sheet1").Columns("A").Find(startDate, _
LookIn:=xlValues, lookat:=xlWhole).Row
stopRow = Worksheets("sheet1").Columns("A").Find(stopDate, _
LookIn:=xlValues, lookat:=xlWhole).Row
Worksheets("Sheet1").Range("A" & startRow & ":A" & stopRow).Select
End Sub
I am getting error in Set MyRange
[Code] .....
I attempted to modify "macro_1a1ay" to look into the "comments" sheet (column a) for a specific text string. If that text string is found, I have it delete the entire row, then re-sort the page and return to the calling page. It works well as long as it finds something. When it does not find the string (i.e. like now when the page is blank) it gives me the run-time error mentioned above.
View 7 Replies View RelatedI am having a lot of trouble finding out why I am getting error. I believe the error is because it can't find the number. In cells C115:C314 i have the numbers 1 to 200 in order. when someone types in 1 to 9 in the text box it works, but on 10 and over i get the error ???? here is the code I have
shCalculator.Range("C115:C314").Select
Selection.Find(what:=CInt(txtPackageID), After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Select
x = ActiveCell.Row
shCalculator.Range("ProposedMeter").Value = Cells(x, 7).Value
shCalculator.Range("Package").Value = Cells(x, 12).Value
shCalculator.Range("ProposedMeterAmount").Value = Cells(x, 30).Value
shCalculator.Range("Term").Value = Cells(x, 62).Value
shCalculator.Range("Discount").Value = Cells(x, 67).Value
shCalculator.Range("Equipment").Value = Cells(x, 72).Value
I have two macros that run fine separately. I want to join them, but when I do I get an Object variable or With block variable not set error.
Here's the code
Code:
Sub border_highlight_insert()
' Change heavy border to new column, change tint - keyed to year A6
With ActiveSheet
Dim i As Integer
[Code]....
This works fine in Excel 07 but when ran in 03 it doesn't work and I get that error message.
Sub mcrRefresh2()
Sheets("WeeklyData").Range("A1").ListObject.QueryTable.Refresh BackgroundQuery:=False
Sheets("WeeklyData").Range("aa1").ListObject.QueryTable.Refresh BackgroundQuery:=False
Worksheets("WeeklyPivot").PivotTables("PivotTable1").PivotCache.Refresh
End Sub
I am getting an error at this line:
Set rng5 = Range(.Cells(celle2.Row, celle4.Column), .Cells(celle2.Row, celle4.Column))
Sub UpdateNumbers()
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim celle1 As Range
Dim celle2 As Range
Dim celle3 As Range
Dim celle4 As Range
Dim celle5 As Range
Dim flag1 As Long
Dim flag2 As Long................
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 RelatedFunction Find_Range(Find_Item As Variant, _
Search_Range As Range, _
Optional LookIn As Variant, _
Optional LookAt As Variant, _
Optional MatchCase As Boolean) As Range
Dim c As Range
If IsMissing(LookIn) Then LookIn = xlValues 'xlFormulas
If IsMissing(LookAt) Then LookAt = xlPart 'xlWhole
If IsMissing(MatchCase) Then MatchCase = False
With Search_Range
Set c = .Find( _
What:=Find_Item, _
LookIn:=LookIn, _
LookAt:=LookAt, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=MatchCase, _ ........................
Using the message box I see that sheet 1 opens but then I receive an error message Run Time Error 91, Object Variable or With Block Variable not set. I tried declaring and using set on "project" but got nowhere. I also need to have a message indicating project not found. Once this part is solved I will loop all of my other workbooks
I've got a simple Macro which manipulates data in various worksheets. Problem is that I get the error: "object variable or with block variable not set" in the area of the code that I have put in bold below:
All I'm trying to do in this part is find the first occurrence of the #n/a value ....
I've set up a userform (frmCoC) to read and write info to the "contractor database". Within that userform is a listview control that reads off a second, "induction database".
When the user selects and clicks on one of the displayed entry in listview, another form (frmInd) opens to edit data on the induction database.
There's a command button along side the original form (frmCoC) to create a new entry in the induction database. The click event for the button is as follows:
I only get the error 91 "object variable or blocked vairable not set" when the below code is used or loaded in Internet Explorer IE 9 browser but works fine in Excel.
Public Sub CommandButton9_Click()
Dim myDir As String, fn As String, wb As Workbook, rng As String
myDir = "C:DatabaseCust_Name"
fn = TextBox2.Text
On Error Resume Next
Set wb = Workbooks(fn)
If wb Is Nothing Then
[code].....
I'm trying to run some code but I keep getting the Object Variable or With Block Variable Not Set message. I'm not using any object in the line that is causing the error.
I'm getting an error on the last line of given code
Code:
Private Sub addDescriptions(BucketTotal as Double, Descriptions as String)
Dim DCELL as Range
'''Loop Through Controls'''
For i = 1 to 20
if Controls("TR" & i).Value = "" Then
Exit For
Else
Set DCELL = Columns("F").Find(Controls("DivRate" & i), Lookin:=xlValues, Lookat:=xlWhole).Offset(0,3)
At a high-level: My code fails in the sub-routine that is called (SecondSub). The first line of code on the SecondSub is where I get the Error 91 Object Variable or with block variable not set.
1st Routine:
________________________________________________________
Sub FirstSub()
Dim ie As Object, iebody As String, strURL As String, strUsername As String, strPassword As String
Dim lRow As Long
Dim abc As String
Dim striEst As String
Dim ele As Object
Dim LinkHref As String
Dim a As String
Dim b As String
Dim c As String
a = DateAdd("d", -1, Now)
b = DateAdd("d", -2, Now)
c = DateAdd("d", -3, Now)
Set ie = CreateObject("InternetExplorer.Application")
strURL = "website"
strUsername = "xxxxxxxxxxx"
strPassword = "xxxxxxxxxxx"
While ie.busy
DoEvents
Wend......................
I have my below code which will create a looping "ftp" and "get" effect.
However i am having some problems right now. There's always an error that states "Object variable or With block variable not set".
Sub File_Transfer_testing()
Dim N As Integer
Dim Lotid As Variant
Dim Tester_array(1 To 15) As String
Tester_array(1) = "10.10.10.10"
Tester_array(2) = "20.20.20.20"
Lotid = InputBox("Input a LOT ID")
N = 1
Set fs = CreateObject("Scripting.FileSystemObject")
Set A = fs.CreateTextFile("C:script.txt", True)
For N = 1 To 2
A.writeline "open " & Tester_array(N)
A.writeline "xxx" 'username
A.writeline "xxxxxx" 'password
A.writeline "cd /zzz/reports"
A.writeline "prompt"
A.writeline "ascii"
A.writeline "mget " & Lotid & "*sorts" 'file to be uploaded
A.writeline "quit"
A.Close
Set fs = CreateObject("Scripting.FileSystemObject")
Set A = fs.CreateTextFile("C: upload.bat", True)
A.writeline "ftp -s:C:script.txt" 'the ftp site
A.Close
dRetVal = Shell("C:upload.bat", 0) 'upload the file
''''''''''end upload.bat file
Next
MsgBox ("Done")
End Sub
I have the following code (just pasting the relevant section) which crashes when it reaches the highlighted line of code. and a dialog box pops up with the text: "Object variable or With block variable not set"
Sub test()
Dim StartRng As Range
Dim Buffer As Range
Set StartRng = WorkSheets("Sheet1"),Cells(1,1)
StartRng.Activate
ActiveCell. CurrentRegion.Select
Buffer = rngStart.CurrentRegion.Copy
' I also tried the following line of code but that didn't work either
'Set Buffer = rngStart.CurrentRegion.Copy
..
...
End Sub
I have used this type of thing a few times before with no problems, but now I am getting the error in the thread title on the cells.find line:
View 3 Replies View RelatedI have created a form, that users will use to input data. I want to provide a template excel workbook, that they are supposed to copy, and save with their own file name leaving the template untouched. The following code is supposed to check to see if this is the template workbook, and if so, it will open an worksheet (otherwise hidden) to allow them to do a "save as". Everytime I try to run the code I get the following error..."object variable or With block variable not set" and I don't know what I'm doing wrong. The only thing I did find was if I removed the "unload me" I didn't get the error. However, I need the form to unload so that the user can save the file under a new name.
View 9 Replies View RelatedI am trying to call one webMethods service from excel. The code is generated by webMethods. I can compile the code without any error. But while running the code I am getting this error "Object variable or with block variable not set".
Option Explicit
Private wc As Context
Private CError As String
Private outputs As New Values
Private isConnected As Boolean
Public Function TestWebServie(in_name As Variant) As String
On Error Goto Err_TestWebServie
Dim inputs As New Values
If isConnected = False Then
CError = Connect
End If
If Len(CError) = 0 Then
inputs.put " name", in_name
setStatus ("Invoking service Default.SukantaTestWebServie...")
Set outputs = wc.invoke("Default.Sukanta", "TestWebServie", inputs)
Else
TestWebServie = CError
End If
clearStatus...........
Hit a runtime error 91 "Object variable or With block variable not set" at the Loop Until Point.
Private Sub UpdateOldPL()
Dim n As Range, gg As String
Dim Delete As String
Delete = "CHECK"
With Range("C5:C65536")
Set n = . Find(Delete)
If n Is Nothing Then Exit Sub
gg = n.Address
Do
n.Offset(0, -1).FormulaR1C1 = "=VLOOKUP(RC[-1],'Unrlized_P&L(Dt_of_Rpt)_t-1'!C[-1]:C[9],2,0)"
n.Offset(0, 3).FormulaR1C1 = "=0-VLOOKUP(RC[-5],'Unrlized_P&L(Dt_of_Rpt)_t-1'!C[-5]:C[5],10,0)/1000"
n.Offset(0, 4).FormulaR1C1 = "=0-VLOOKUP(RC[-6],'Unrlized_P&L(Dt_of_Rpt)_t-1'!C[-6]:C[4],11,0)/1000"
n.Offset(0, 5).Value = "SOLD"
n.Value = 0
Set n = .FindNext(n)
Loop Until gg = n.Address
End With
End Sub
I'm writing a little program for a co-worker of mine to make their job a little easiar. Here is the code that I have written so far. The first 2 parts of it work perfectly fine, but if it gets to the 3rd part I get an Object Variable or With Not Set. I understand this is horribly written code as I am kind of a beginner and just do things by trial and error.
Sub Test3() ....
Am getting Object variable not set error in the selection.find code. I dont see anything wrong with the code..
VB:
rivate Sub Btn_Edit_Click() ' code for Edit Function
Dim findvalue As String
Dim FileName As String
[Code].....
I can see the value of "findvalue" do exist in the column "A:A".
I am trying to make this code work, I am getting the subject error:
HTML Code:
Private Sub CommandButton1_Click()Dim cols As VariantDim NextRow As ObjectDim Myrow As Object
Sheets("Sheet1").SelectWith Sheet1
Myrow = Range("A" & Rows.Count).End(xlUp).Row + 1
[Code] .....
My code as below
Application.Goto Reference:="CURRENT"
Selection.Copy
C1 = Range(Cells(4, 8), Cells(4, 200)).Find("HC4").Value.Row + 1
C1.Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
I want to copy the range 'Current' , this part, the easy part works.
Next I want to look in the range of H4 to GR4 for the value that also appears in HC4
In my example this would be CW4 then I want to paste 'Current' to the cell 1 row below, ie CW5
I just want to set a variable to a textbox object, but everything I try to do just trys to set the variable to the textbox text. Here's the
Dim tb As textbox
Set tb = userform.textbox3
I tried a couple variations, but it just keeps trying to set the textbox variable to whatever is input into the actual textbox.