Set Range Variable To RefEdit Control
I'm trying to set up a userform with a refEdit box that allows the user to select a cell range. I then need to assign that cell range to a range variable so the macro can use it. I'm having a problem reconciling the assigned string variable that refEdit returns and the range variable that I need.
So what I'm trying to say is that I can assign the range fine through the Userform, and I can see through the debugging screen that mySampleVariable (dim as a variant) holds a range value of 'Access Dump'!$A$8 (the correct range), but when I try to
Dim myRange As Range
Set myRange = mySampleVariable
The code bugs out because it apparently can't assign a string value to a range variable.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
To Set Focus To The RefEdit Control In Userform
I am trying to implement a simple userform using the RefEdit control. So I have the RefEdit control and an Ok (which has code attached to it) and a Cancel commandbutton. For some reason, I can't get the focus on the RefEdit control (i.e. when I activate the form, I have to actually click in the RefEdit box before it gets the cursor). Which property sets the focus in this control? Right now I have the Ok button Default property set to True. I have a commandbutton on the spreadsheet that activates the userform.
View Replies!
View Related
Get Range Specified In Refedit Control
I have built a user form that amongst it's other functions displays the results from three cells on one worksheet. The values in these cells need to be pasted in three cells on a different worksheet (cells are selected by user using a "RefEdit" field on the form. The VBA help files offer a single example of the paste special method (I've proven I can paste to the celss but of course that pastes the formula rather than the value!) that shows it operating on a single worksheet as follows: With Worksheets("Sheet1") .Range("C1:C5").Copy .Range("D1:D5").PasteSpecial _ Operation:=xlPasteSpecialOperationAdd End With So, simple question... How can I use the Paste Special method to paste the values of the cells in cells specified by the user?
View Replies!
View Related
RefEdit Control To Click On The Desired Range
I am using a refEdit control on a userform to get a range from the user (they use the refEdit control to click on the desired range). My problem is that the user typically has 3 or 4 different workbooks open and if one of them is maximized withing Excel, the user is unable to get to them to select the range (only the maximized workbook is displayed and usable). Is there a way around this?
View Replies!
View Related
Set Variable To Forms Control & Get Value Property
How do I reference a spin button from a variable that I set. e.g Dim objSpn As Object Dim y As Integer Set objSpn = ActiveSheet.Shapes("spnWCDate") y = objSpn.Value This doesn't work, but hope it explains what I want. It's so I dont have to write the whole object name each time I reference it. ADDED: I've just noticed that this doesn't work even i do reference the object with the full name. I copied this from a forms spin control that I was using, why can't i store the value of the spin button in a variable?
View Replies!
View Related
Refedit Control In Worksheet
I would like to use the RefEdit control in a worksheet in order to select a range of cells and pass its address to a different cell without code. I thought of using the LinkedCell property of the RefEdit as I used it for a ComboBox.
View Replies!
View Related
Refedit Control Failing In 2007
I'm finding that my add-ins that worked perfectly on Excel 2003 are not functioning on Excel 2007. One of the main issues seems to be loading any UserForms that use the RefEdit control. I get a message saying: -------------------------------------------------- Run-time error '459': Object or class does not support the set of events --------------------------------------------------
View Replies!
View Related
Set Range Variable Using Variable Row Number
I am using a variable named " Totals" as a range type to refference the range in a formula. It works the way I have it. Dim Totals As Range Set Totals = [U37: AE37] Now instead of the absolute refference, I would like to change the row refference by an offset of my current row, using a formula with a varriable. The columns stay the same.
View Replies!
View Related
Set Range Variable To Growing Dynamic Range
I have been working on part of the code for my spreadsheet and it works fine in the spreadsheet “Databaseform” however when I copied the code to my master spreadsheet “Paul_PartLocDBCombo” it does not work, I get the error: Method ‘ range’ of object ‘_worksheet’ failed The code is then highlighted in yellow, the code is: Set rng = wksPartsData.Range("a1", Range("a65536").End(xlUp)) Meaning this part is incorrect but I don’t know why? To work it: go to Databaseform and press start. Enter 7mm in the product field and press find all. It will then return all the matching results in the userform. Its this I want to try and achieve on the other spreadsheet when the button find label is pressed.
View Replies!
View Related
Set Range As Variable
I searched and I seached but I cant find the answer. I want to paste a set of cells in a blank range that is next to data already in the workbook. I cant get the right wording for the variables in the range I want to paste to. Here is what I got so far, the red text is what I am getting hung up on. (ya I know it sucks) Dim mycel1 As Range Dim mycel2 As Range Set mycel1 = Range("A1").End(xlToRight).End(xlDown).Offset(0, 6) Set mycel2 = Range("A2").End(xlToRight).Offset(0, 1) Range("N2").select Selection.Copy Range(mycel1, mycel2).Paste
View Replies!
View Related
Set Variable Range
I have reached a point in my code where I have a variable range selected. I want to autofill the selected range of data to a new range. My code is: Range(Cells(7, LastReportColumn - 1), Cells(LastReportRow, LastReportColumn - 1)).Select Set DestinationRange = Range(Cells(7, ReqMonths + 5), Cells(LastReportRow, ReqMonths + 5)) Selection.AutoFill Destination:=(DestinationRange), Type:=xlFillDefault The select part works. However on the second line (set DestinationRange) I get a type missmatch.
View Replies!
View Related
Test If Range Variable Set
I'm trying to test whether a variable, a range in this case, has already been defined. If not I want to Set it to a specific cell. I tried: If rng Is Nothing Then Set rng = Range("B14") Else rng = rng End If
View Replies!
View Related
Variable To Set A Column Range
I am trying to set a range for a for next loop using the result of a find, the location of the find result will always be row 1 but may be in any column after Column D. I have been trying different ways to work out this issue but have failed in all my attempts. I would be very grateful to anyone who can tell me where I am going wrong (I am sure it will be very simple). For Each shtRegisterSheet In ActiveWorkbook. Sheets shtRegisterSheet.Activate Set rngFindBalance = shtRegisterSheet.Range("c1:da1").Find(what:="Balance") dteSaledate = Format(rngFindBalance.Offset(0, -1), "yy") dteYeardate = Format( Date, "yy") If dteSaledate < dteYeardate Then ......................
View Replies!
View Related
Clear Set Range Variable
is it possible to clear the value in a range variable? For example say: Set A = Range("B4") Is there a way to clear the value of that range, in other words make it blank again? And unfortuanlly i can't just make it equal to another range i need it to be blank.
View Replies!
View Related
Set Variable To End Of Range
still trying to figure it out how the logic in VBA works 'this selects a block of sells and put them in variable rormu. Right? Set test = ActiveSheet. Range("i65535").End(xlUp).Offset(-1, -3) Set test2 = ActiveSheet.Range("i65535").End(xlUp) Set formu = Range(test, test2) 'here i try to create another variable for a range after last used row Set newg = ActiveSheet.Range("f65535").End(xlUp).Offset(2, 0) Set newg2 = ActiveSheet.Range("f65535").End(xlUp).Offset(3, 3) Set pst = Range(newg, newg2)
View Replies!
View Related
Set Variable: Object Variable Or With Block Variable Not Set
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
View Replies!
View Related
Set Range Variable To Last Used Cell In Column
how (if possible) to activate the link between the Excel spreadsheet and the VBA editor, so when I cut/paste row, columns and cells in my spreadsheet, my VBA code is automatically updated. I have a rather large excel tool combined with a significant amount of VB code, and each time I insert/remove a line, I have to re-type the cell references,
View Replies!
View Related
Set Variable To Named Range Or Single Cell
The problem exists with the lists I use. I have a "settings" sheet, where users can choose from preset values to run in the program. E.g. "Urgent", "Semi Urgent" and "Non Urgent" (there are a lot more values, but those will do for now." and the idea is to run them 1 at a time. However, I've now determined that 99% of the time, they're going to be run 1 after another, that is to say run the program once for "Urgent", then again for "Semi Urgent" and then again for "Non Urgent". So I'd like to loop through these. Again, not a problem, you'd think. So I added a new value to the list; "All" which users can select to invoke a list. Again, not a problem, I already have the list as a named range, "DmgList" and I can cycle through it. I ran the following test code to see if it would work in theory...
View Replies!
View Related
RefEdit To Capture Range For Other WB
I have a userform with a 3 refedit controls. Each needs to capture a range from a different workbook (already open - only not active). Is it possible to configure the refedit to capture a range from another workbook (either thru window menu or ALT+TAB)? I notice that InputBox type:=8 allows it - I'm a little suprised that default refedit doesn't.
View Replies!
View Related
Macro Error Message (object Variable Or With Block Variable Not Set)
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 Replies!
View Related
Find Dates Macro: Object Variable With Block Variable Not Set
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
View Replies!
View Related
Run Time Error 91 Object Variable Or With Block Variable Not Set
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 Replies!
View Related
Find Method Code: Object Variable Or With Block Variable Not Set
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
View Replies!
View Related
Run Time Error 91, Object Variable Or With Block Variable Not Set
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 Replies!
View Related
2007 Macro: Run-time Error 91:Object Variable Or With Block Variable Not Set
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 Replies!
View Related
Runtime Error 91, Object Variable Or With Block Variable Not Set
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 Replies!
View Related
Debugging Error: Object Variable Blocked Variable Not Set
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 Replies!
View Related
Object Variable Or With Block Variable Not Set Error With Macro
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 Replies!
View Related
Find Method: Object Variable Or With Block Variable Not Set
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 Replies!
View Related
Error: Object Variable Or With Block Variable Not Set
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 Replies!
View Related
Error 91 Object Variable Or With Block Variable Not Set
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 Replies!
View Related
CreateObject: Object Variable Or With Block Variable Not Set
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
View Replies!
View Related
Object Variable Or With Block Variable Not Set
I 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 Replies!
View Related
Object Variable Or With Block Variable Not Set ...
I 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...........
View Replies!
View Related
Runtime Error 91 "Object Variable Or With Block Variable Not Set"
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
View Replies!
View Related
Set Control Visibility Based On Tag Property
I have a userform which uses the tag property of the frame to determine whether a frame is visible (and hence the controls that reside within the frame). A frame contains three combo boxes, and six text boxes. The tag property of the frame matches the number of frames that are visible on the form, so that if the user selects five frames, frames 1 through five become visible and for all other frames visible = false. A frame contains all the data for a single entry. There can be up to fifty frames/ entries that are visible on the form depending on the user selection. I want to use the visibility property to do two things: first the combo boxes are filled from an array after the user selects the number of frames (or entries). I only want to fill the comboboxes where the frame is visible. The second thing is that I have a function which uses the data from the text boxes and combo boxes as required arguments. Since these are required arguments, I will get a data mismatch error if I try to call it and the controls are empty. Therefore, I only want to call the function if the frame which houses the controls is visible. Public Sub Visible1(Entry As String) Dim ctrl As Control For Each ctrl In UserForm2.Controls If TypeName(ctrl) = "Frame" And ctrl.Tag <= Entry Then With ctrl .Visible = True End With Else If TypeName(ctrl) = "Frame" And ctrl.Tag > Entry Then With ctrl .Visible = False.............
View Replies!
View Related
Set Label Control Captions To A Cell Text/Value
I have an excel application that I'm developing that is going to be used by two different departments. On one side everything will pretty much be userforms. The sheets they the data I need to pull in for them is on sheets that I have such that their visibilty is equal too xlveryhidden. I'm trying to change the captions on labels using the data on the veryhidden sheets. Something like this. 'frmCurrentProposal is a userform 'lbGroupName and lbGroupNum are both labels that should vary frmCurrentProposal.Show frmCurrentProposal.lbGroupName.Caption = Sheet2. Range("B2").Value frmCurrentProposal.lbGroupNum.Caption = "Group # " & Sheet2.Range("B3").Value The labels are not changing their values.
View Replies!
View Related
Value For Variable Based Off Control Checkbox.
I have several checkboxs in an excel sheet, that if one is checked i would like it to make a change to a variable in a macro that will run when saving a file. for an example: if checked: Checkbox1 = NCE1 Checkbox2 = NCE2 Checkbox3 = NCE3 Checkbox4 = NCE4 Checkbox5 = NCE5 I have a bit of code that is like this.... ActiveWorkbook.SaveAs ("\marketing2PartageNonconforms" & sF1 & " - CO" & sF2 & " - FC" & sF3 & ".xls") I would like sF1 to change depending on which checkbox is checked. so if checkbox1 is checked, than sF1 = NCE1 .
View Replies!
View Related
Set Text & Hide Control On UserForm Show/Load
I'm trying to do is set up a form load event to initlize some controls. Here is my Private Sub Form_Load() 'Initialize the form lblProcessing.Visible = False txtFileName.Text = "Enter a file name" End Sub At the moment, this event is not triggering. I have the code in the code behind my form - should it be in a module? PS: This site is great - it's answered a lot of my other questions so far without me having to make any posts.
View Replies!
View Related
Find And Find Again - Object Variable Or With Block Variable Not Set
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
View Replies!
View Related
Object Variable Not Set ..
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.
View Replies!
View Related
Object Variable Not Set
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() ....
View Replies!
View Related
Set Public Variable
Setting the Public Statement does not work. These are my codes: Option Explicit Public Ankis_makro As Boolean Sub Makro2_Ankis_veckor() On Error Resume Next Application. ScreenUpdating = False Sheets("Listan").Activate Selection. AutoFilter Field:=1 Selection.AutoFilter Field:=2 Selection.AutoFilter Field:=3 Selection.AutoFilter Field:=4 Selection.AutoFilter Field:=5 Selection.AutoFilter Field:=6 Selection.AutoFilter Field:=7 Selection.AutoFilter Field:=8 Selection.AutoFilter Field:=9 Range(Range("J2"), Range("I65536").End(xlUp).Offset(0, 1)).FormulaR1C1 = "=TEXT(RC[-9],""ĹĹĹĹ"")" ................... What ever I do I can't get the Ankis_makro set to True.
View Replies!
View Related
Set Variable To Value Of Cell
how to set a named variable to a value from a cell without using .select on that cell? This is what I tried without success. Set peaktime = activecell.formulaR1C1 = "=RC[-1]" While I get an error when this line attempts to execute, the variable actually has the correct value assigned to it when I mouse over it in debug mode. If I use that exact same line without the 'set', peaktime equals 'false' instead of the value in the corresponding cell.
View Replies!
View Related
Set Variable As Book Name
I am generating Excel reports (with different names) using VBA. The problem I'm having is that for a certain macro, it needs to create a new book, and then activate the original window. But because all the new reports have new names (but using same macros), the name written in code doesn't equal the book's new name. How can I set a variable to be the book name? I do know that the name of the original book will be equal to two letters plus the value in A7. The problem is this: I have written a macro that shows the following Worksheets("Sheet2").Select Workbooks.Add Windows("FT Bi Test.xls").Activate I need something more like this (though this exact code doesn't work): Worksheets("Sheet2").Select Workbooks.Add Windows("FT " & Range("A7").Value ".xls").Activate Or something like this: Worksheets("Sheet2").Select Workbooks.Add Windows("FT " & Variable ".xls").Activate
View Replies!
View Related
How To Get Set Of Chars From A String To A Variable ..
CALIB/SENS,S(S5a90b-90),FA(CAL_15) This one has two sets of quotes, with the information from the noted post I can retrieve the string "S5a09b-90", now that I have a double set of brackets "( )" how do I get the value for the second set of brackets, "CAL_15"? In the quoted string, CALIB/SENS,S, is constant. The first set of brackets are constant (string length is not), the second set of brackets are constant (string length is not).
View Replies!
View Related
|
TRACKER
