Looping: Run The First Macro An Error Pops Up Saying That A Variable Within The Loop Is Not Set
Mar 27, 2007
the following code should determine whether the searched value can be found in more than just one row and than enlist certain values from each of those rows in ComboBox4 using a loop. Then I have a second macro which would assign appropriate values from a Sheet to other text boxes whenever one changes the value of the ComboBox4:
Private Sub ComboBox2_Change()
Dim vFind
Dim Firstaddress
Dim rFound As Range
Dim wsName As String
Dim SrchRng As Range
wsName = Me.ComboBox1.Value
With Worksheets(wsName)
vFind = UserForm2.ComboBox2.Value
Set rFound = .Range("B1")
Set SrchRng = .Range("B:B")
Option Explicit
End With...............................
While trying to run the first macro an Error pops up saying that a variable within the loop is not set. I've got no idea how to fix it
View 9 Replies
ADVERTISEMENT
Jun 13, 2014
I would like to create a change event macro that will update multiple formulas that pull data from various tabs on a file. The tabs are named with a single date. So ideally when a certain master cell (Allocation!H2) is updated to the current date the macro will be prompted to update the range that contains formulas and update accordingly...so its and index match fomula based that has a variable being the tab name which is a date. I have a range of dates in cells F4:AB4 and want the formula to go into F5:AB5, I can then fill that down to whatever row I want...that bit I can handle.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Worksheets("Allocation").Range("H2")
[Code]......
View 4 Replies
View Related
Sep 11, 2013
What is this error message that is popping up when I try to insert a blank row of cells?
"To prevent possible loss of data, Excel cannot shift nonblank cells off of the worksheet. Select another location in which to insert new cells, or delete data from the end of your worksheet.
If you do not have data in cells that can be shifted off the worksheet, you can reset which cells Excel considers nonblank. To do this, press CTRL + End to locate the last nonblank cell on the worksheet. Delete this cell and all cells between it and the last row and column of your data then save."
This excel document we have has LARGE amounts of data
View 3 Replies
View Related
Feb 20, 2009
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.
View 9 Replies
View Related
Jul 7, 2009
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
View 9 Replies
View Related
Mar 12, 2009
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 Related
Oct 20, 2006
Excel file attached! I need a macro to do the following:
Start with cell F4 and read the increment value from cell C4. Then add this with F3 and display the result. Continue with the same increment until it reaches the value equal to cell B4. Then read the increment from cell C5 and do the same until value equals cell B5. Repeat the same step until it reach the value equals B7. I did manually in the column F4 to F28. Moreover, the cell increment will change according to the variable in A2. In this case it is 25 and got 25 values to fill the column R.
View 8 Replies
View Related
Sep 22, 2007
Another question...
I want to organize the data by broker then by security, I created the following code to do the looping...but it stopped loop and generated information for one broker....Do you know what is wrong with my code?
I also want to sum the results of cell (A,7) based on broker...basically I want to insert a new row to sum the results of one broker, before going to the other...how should I do that also?
Sub OJOM1()
Dim A As Integer
Dim B As Integer
Dim Broker As String
Dim OJOMM As String
A = 2
'Do While Worksheets("historydata").Cells(A, 1) <> ""
B = 1
Do While Worksheets("BrokerList").Cells(B, 1) <> ""
If Worksheets("BrokerList").Cells(B, 1) = Worksheets("historydata").Cells(A, 6) Then
'= Worksheets("BrokerList").Cells(B, 1) Then
Broker = Worksheets("BrokerList").Cells(B, 1)
'MsgBox Broker
Do While Worksheets("historydata").Cells(A, 1) <> ""
If Worksheets("historyData").Cells(A, 6) = Broker And Worksheets("historydata").Cells(A, 2) = Worksheets("MarginReq").Range("B4") And Worksheets("historydata").Cells(A, 3) = Worksheets("MarginReq").Range("B5") Then
Application. ScreenUpdating = False
Worksheets("historydata").Cells(A, 6).copy Sheets("MarginReq").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.ScreenUpdating = False
Worksheets("historydata").Cells(A, 7).copy Sheets("MarginReq").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)
Application.CutCopyMode = False
Application.ScreenUpdating = True
End If
A = A + 1
Loop
End If
B = B + 1
Loop
'A = A + 1
'Loop
End Sub
View 4 Replies
View Related
Aug 12, 2009
Why wont this loop thru all worksheets? It seems it runs worksheet named "Monday Wk (1)" for each sheet and no others.
View 9 Replies
View Related
Feb 9, 2010
I am doing wrong here, the code seems to keep looping. I broke out of the search and its found the correct information and copied the correct rows, though looped and did it again, and again, and again,
View 2 Replies
View Related
Feb 12, 2009
I can't work out why this keeps looping. Can anyone help, please? Thanks.
Sub Test3()
LastRow = Range("B65536").End(xlUp).Row
Do
Do
ActiveCell.FormulaR1C1 = "=R[-1]C"
ActiveCell.Offset(1, 0).Select
Loop Until Not (IsEmpty(ActiveCell.Offset(0, 0)))
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Row = LastRow
End Sub
View 9 Replies
View Related
Jun 22, 2014
I wonder how to loop and control throught 2 different variables?
[Code].....
I mainly want the macro to do:
[Code] ....
Do not consider the rngList.Cells, but more the mathematical way.
View 4 Replies
View Related
Oct 19, 2006
I have a sheet which is using a single input value to calculate an output. On the other side of the sheet, I am using goalseek to find what input value would give me a specific output value - this is pre-set.
Starting in row 12, I am using the macro to paste the goalseek solution into a new column at the end and then resetting the original input value. The macro then moves onto row 13, repeats and loops and so on.
My questions are:
1) how can I get the macro to count the number of rows with data in it instead of fixing it at 217 (as seen in "For x= 12 To 217")?
2) how can I get the goalseek goal to reference a user defined cell instead of being fixed at 37 (as seen in "Cells(x, 13).GoalSeek Goal:=37, ChangingCell:=Cells(x, 5)")?
3) after it fills col15 with the goalseek solution to 37 for all the rows, how can I then start a new loop to tell it to start filling col16 with the goalseek solution to say 50, and then col17 with the goalseek solution to say 55? ..
View 4 Replies
View Related
Jan 7, 2010
The code below first asks a user to input a weekending date (which must be a Saturday). The value of the input box goes to cell C1 of my spreadsheet. That part works fine. I also have a function in Cell G1 with the function:
=TEXT(C1, "dddd")
I don't know if this is the best way to test for a Saturday but it is what I have,
Where is goes bad is if the date is not a Saturday. I have a loop to force a new date install via a input box but it won't update C1 with the inputed data to recheck for the Saturday value. Below is the whole code I am working with...
Dim aa As String
Dim bb As String
If Range("C1") = "" Then
Do While bb = ""
bb = InputBox("Please Enter a Weekending Date!")
Loop
Range("C1").Value = bb
End If
If Range("G1") "Saturday" Then
Do While aa "Saturday"
aa = InputBox("Weekending Must Be a Saturday. Please Enter a New Weekending Date")
Loop
Range("C1").Value = aa
End If
View 9 Replies
View Related
Jan 29, 2014
The code below works fine on smaller data sets. I tried it on a data set of over 165000 records and it gives me the error:
"Runtime Error '6': Overflow"
Clicking debug highlights this line:
rowCount = .DataBodyRange.rows.Count
Code:
Sub Fixtable()
Dim lo As Excel.ListObject
Dim loRow As Excel.ListRow
[Code]....
View 4 Replies
View Related
Apr 22, 2008
I have a peice of code (Below) and instead of going from work sheet 4 to the one in the workbook 2 from the end sheet by sheet running the same bit of code it just loops repeatedly on one sheet??
Sub a()
Dim n As Integer
For n = 4 To ThisWorkbook.Sheets.Count - 2
Columns("W:W").Select
Selection.Copy
Selection.Insert Shift:=xlToRight
Columns("L:L").Select
Selection.Delete Shift:=xlToLeft
Range("L8:L9").Select
Selection.AutoFill Destination:=Range("L8:W9"), Type:=xlFillMonths
Range("L8:W9").Select
Application.CutCopyMode = False
Next n
End Sub
View 9 Replies
View Related
Feb 28, 2013
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] .......
View 9 Replies
View Related
Apr 10, 2009
I have created a form that pops up using a macro when the form is opened. It is a simple form that uses optionbuttons in a group to select Intl or Dom.
Problems:
1) How do you get the form to close once a button is selected?
2) The user can close the form without selecting a button (the X at the top).
3) How do you get the data selected onto the excel spreadsheet?
View 9 Replies
View Related
Mar 24, 2014
I am getting error in Set MyRange
[Code] .....
View 3 Replies
View Related
Oct 7, 2009
I 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
View 3 Replies
View Related
Jul 11, 2014
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]....
View 2 Replies
View Related
Feb 6, 2009
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
View 9 Replies
View Related
Nov 9, 2009
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................
View 9 Replies
View Related
Jan 14, 2007
Function 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
View 5 Replies
View Related
Feb 7, 2007
I am trying to run create a simple macro that copies and paste special values - something I have done 100's of times but for some reason I keep getting an error message - even though I recorded the macro and didnt write it by hand - see below:
Sub Macro6()
Cells.Select
selection.Copy
selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
For which I get 'Compile Error - Expected Function or Variable'
View 6 Replies
View Related
Jun 20, 2007
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 ....
View 12 Replies
View Related
Mar 3, 2009
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:
View 8 Replies
View Related
Nov 17, 2011
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].....
View 9 Replies
View Related
Oct 23, 2009
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......................
View 9 Replies
View Related
Mar 14, 2014
I have a lot of loops, but they are not necessarily nested within each other. Is it possible for me to use the same variable if those loops are not nested.
For example, something like this:
Dim a As Integer
For a=1 To 5
[Lines of code]
Next a
Then I want to just continue using "a" in other loops:
For a=12 To 100
[Lines of new code]
Next a
This way, I don't have to create a new variable every time. Would this be okay? Or is it hard on the memory?
View 2 Replies
View Related