Variable Time Calculation
[code] ......
=SUMIF($A:$A,">="&TODAY()-1,D:D)-SUMIF($A:$A,">"&TODAY(),D:D) calculates the total in Col D looking back from today. The results gives you 12:00. What I need is a formula that will look back from the current time to 24 hours in the past, NOT 1 day, to calculate the total in Col D. Looking back 24 hours, from 10:00am, the results would be 6:00.
Since the first time on 12/12 has not arrive, it will not calculate it, but will look back from 10:00am on 12/12 to 10:00am on 12/11 and calculate a result of 6:00 hours.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Retrieving Variable Rows From Another Worksheet: Reducing "Calculation Time"
I'm using this formula to pick all rows from a huge range of cells "DATA" in another sheet, by matching the first column value (SAPDATA is the range that contains all the first column cells of DATA). {=IF(ROW($B49)-ROW($B$48)>COUNTIF(SAPDATA,$D$4),"",INDEX(DATA,SMALL(IF(SAPDATA=$D$4,ROW(SAPDATA)),ROW($B49)-ROW($B$48)),2))} All possible first column values have been stored as a dropdown in cell D4, and whenever I change the choice in the dropdown cell, the retrieval of those hundreds of rows takes ages in excel.
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
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
Calculation Based On Variable
Im trying to access data from 2 different cells (say A1, B1), its for calculating transformer hotspots. The cooling method of the transformer is naturaly air cooled oil.. until the winding temp reaches 75C ( I have winding temp data laid out on a column) then the fans come on and some constants in the formula change (need to read from the other cell, say B1) so it switches, but the fans stay on UNTIL the winding temp is 50C and then go off, so the characteristics change back to the previous one, so I need to switch back. How do I go about doing this... my current method starts working when it reaches 50 instead of waiting the temp to go to 75 then fall to 50 then do it... im a bit confused.. maybe i shouldnt be doing this on excel... ow well
View Replies!
View Related
Calculation Of Time With Two Different Time Formats
Need a point in the right direction with this one! I want to calculate the difference between two time values, the problem is there two different formats as below:- 1) 520.00 - this is a number format 2) 500:00:00 - this is a time format HHH MM SS What could I use to show I have a 20.00 hour difference???
View Replies!
View Related
Calculation Error After Passing Values To Variable
I currently have a userform which inputs data into two cells, the third then multiplies these two cells together, which is all honky dory. It works, 2 x 2 = 4. But when i put 0.6 for example it really doesnt work. 0.6 x 25 = 25. WRONG. My code in the macro is fairly long winded but will paste anyway. If ComboBox2.Text = "Pounds (£)" Then Sheets("Claims").Range("G16") = TextBox2.Text Else Sheets("Claims").Range("F16") = TextBox2.Text End If Sheets("Claims").Range("B16") = ComboBox1.Text Sheets("Claims").Range("C16") = TextBox1.Text Sheets("Claims").Range("B16") = TextBox3.Text Sheets("Claims").Range("D16") = TextBox4.Text Sheets("Claims").Range("E16") = TextBox5.Text Dim a As Long Dim b As Long Dim answer As Long a = Sheets("Claims").[E16] b = Sheets("Claims").[F16] answer = (a * b) Sheets("Claims").[G16] = answer
View Replies!
View Related
Calculation On Time
I have been burning brain cells trying to figure this out. I get these numbers from an online source and they come in like this: A B C D E 1/1/0912:01AM02:40AM11:18AM07:55PM The times do not come in as times...when I format the cell to time it doesnt change...that is my first problem. What I would need to do to these times is: take B and C and find what time is in the middle of them and put that in a different column. This mess will also need to be plotted on a chart with time by the minute for one day as the X axis. In my example I drew lines on the chart to show what I mean....the blue lines I dont want charted...I use those to find the time in the middle.
View Replies!
View Related
Time Calculation ...
Having been looking round this site for quite some time now and always finding what I needed I am now a registered member who needs your expertise. I have a spreadsheet for which I need to calculate hours worked depending on a few criteria. [data] ... The criteria is that Sat/Eve is 8pm to 6am weekdays and midnight to midnight on a saturday. Sun is midnight to midnight on a sunday, BH is a bank holiday and basic is everthing else. What I want to know is it these columns can be populated automatically using formulas. I would really appreciate it if someone out there is up to completing this challange, as I have to manually populate this at the moment and it can be 5000+ lines long (it takes hours). If i need to change the layout it's not a problem, whatever it takes to automate it has got to be worth the effort.
View Replies!
View Related
Same Time Calculation
I am trying to get a total column that will give the total only when two particular devices are down at the same time. This total will be taken from a long list of downtime entries for different devices but I only want the total when two particular devices are down, for example Devicedatedowntimedateuptimetotal time 102/01/0911:00:0002/01/0911:09:0000:09:00 202/01/0911:00:0002/01/0911:04:0000:04:00 202/01/0902/01/09 103/01/0903/01/09 303/01/0903/01/09 604/01/0904/01/09 204/01/0913:09:0004/01/0913:12:0000:03:00 104/01/0913:02:0004/01/0913:15:0000:13:00 505/02/0905/02/09 total 1/200:07:00 In the example I am just wanting to work out the total time when both device 1 and 2 were down at the same time, above the total would be 7 minutes because for 4 minutes on the 2/1/9 and 3 minutes on the 4/1/9 they were down at the same time.
View Replies!
View Related
Time Calculation
I have a time card sheet that I want to make automatically calculate the time I have worked. It is set up with four columns that can't be changed. The first column is "Time In(TI)", second column "Time Out(TO)", Third column "Time Taken For Lunch(TTFL)", and Finally "Hours Worked". I have no problem getting the formula to calculate the difference in "Time In" and "Time Out" but taking the 30 minutes out of that is causing a rift. If I put the 30 minutes in 00:30:00 format it will take it but it thinks it's 12:30:00 and it displays in 00:30:00 which I want it to display 30. Just to clarify, I am using =(TI-TO)-TTFL in the Hours Worked cell.
View Replies!
View Related
Calculation Of Time Formula
I am trying to calculate the response time between when a phone call comes into my workplace and when the responder calls back...I have created a formula that does this using the times and dates of when the calls were recieved and went back out. This works except when the call comes in on one day and goes back later in the day the next day, making the response time larger than 24 hours. I also have it set up to eliminate 15 and a half hours from the calculation because our place of business is not open during this time. Further details.... The formula currently being used is... =IF(G50=E50, H50-F50+(H50
View Replies!
View Related
Time Spent Calculation
I need to find out the total time an issue was 'open' (not resolved) during business hours. Issues come in and resolution time is based off of a priority. Priority 1 should be resolved in one hour, Priority 2 should be resolved in 4 hours, Priority 3 should be resolved in 1 business day (8 hrs), Priority 4 should be resolved in 2 business days (16 hrs), Priority 5 should be resolved in 5 business days (40 hrs), Priority 6 should be resolved in 10 business days (80 hours). I have start dates/times field in a column (mm/dd/yyyy hh:mm) , a resolution dates/times field in another column (mm/dd/yyyy hh:mm) and a priority in another column (1-6). I need to calculate the time an issue is in open status (not resolved) based on it's priority but I need for it to take into consideration business hours (Monday thru Friday...7 am to 7 pm)...and not calculate time outside of 'business hours'.
View Replies!
View Related
How Do I Reduce Calculation Time
I am a big fan of huge files (30MB+) all interlinked with array formulas ...and lots of them. The problem is...one calculation take literally 5 hours. (I have one file I try to calculate since cca 4 hours now...it's at 63%) I keep getting extra memory with no visible improvement on the processing time. Is there a trick out there that I can use? Something is clearly wrong. I feel I will soon have to give up Excel, or my formulas, or my job ...
View Replies!
View Related
Formatting With Time Calculation
I have a calculation whereby I use two variables which are 1. Man hours left in the week formatted as [hh] and 2. Amount of work left to do in the week formatted as 0" Man Hrs" I need to carry out a calc which says:- Man hours left in the week - Amount of work left to do in the week. The answer should tell me if I have a deficit or surplus of hours which can then use for planning purposes. Unfortunately the problem I have is that I can't make the formatting the same for both unless someone knows of a workaround. Man hours left in the week = 128. Man hours required to complete all work this week = 224. Therefore I need to say 128-224 = -96. However when I do this this an answer of 16 is returned.
View Replies!
View Related
Textbox Time Calculation
I'm attempting to calculate the total number of hours between a start time and end time using textboxes on a Userform. Here is my latest attempt: Private Sub D24_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim myvalue As String Dim myhour As String Dim myminutes As String myvalue = D24.Value Select Case Len(D24) Case 1 myhour = "0" & Mid(myvalue, 1, 1) myminutes = "00" Case 2 myhour = Mid(myvalue, 1, 2) myminutes = "00" Case 3 The above codes are an attempt to force an entry as time (7, 07, 700, 0700=7:00 / 13, 1300=13:00 etc.). However, the result is not correct. For example: Start Time in Textbox(D24)=07:00 End Time in Textbox(D25)=17:30 Result in Textbox(D27)=0.4375 Desired Result in Textbox(D27)=10.5 I've never calculated times in textboxes,
View Replies!
View Related
Subtotals And Time Calculation
I am trying to calculate the time spent on each task. I have column A with Task IDs and column B with Time. Column A could have the same task ID repeated but the column B has the time for each entry. There is only start time for each task. Where the Task is repeated, want to know how many times the task has taken place and overall time spend on one task ID, calculating from the first entry to the last entry.
View Replies!
View Related
Time Calculation With Colored Cells
I was looking for a solution through the excel questions but could not find anything which would help me. As you can see in column B are two different colored Sun, red and magenta as well as Mon and Tue. I would like to find a formula or macro without creating an extra column which looks for the color magenta i.e. if the particular cells are magenta (Sun, Mon and Tue) then perform an action. I cannot use the formula in K7. ******** ******************** ************************************************************************>Microsoft Excel - Time Calculation with color cells.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutE4F4H4K4E5F5H5K5E6F6H6K6E7K7E8K8K9E10F10H10K10K11K12E13F13H13K13E14K14E15K15E16K16E17F17H17K17E18H18K18= ABCDEFGHIJK1Month: 2Working - Hours 15% 35% 3DayWeekdayStartEndHoursStartEndHoursStartEndHours414Fri20:000:0004:0020:000:0004:00 515Sat0:006:0006:000:006:0006:00 615Sat20:000:0004:0020:000:0004:00 716Sun0:006:0006:00 0:006:0006:00816Sun19:000:0005:00 19:000:0005:009?? 1022Sat20:000:0004:0020:000:0004:00 1123Sun0:006:00 0:006:0006:001223Sun19:000:00 19:000:0005:001324Mon0:005:0005:000:005:0005:00 1424Mon20:000:0004:00 20:000:001525Tue0:006:0006:00 0:006:001625Tue19:000:0005:00 19:000:001726Wed0:005:0005:000:005:0005:00 18 54:00 28:00 22:00Timesheet [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View Replies!
View Related
Calculation Time And Charge Rates
I am trying to figure out a formula to figure out how much to charge for hrs of a rental. There is an automatic $1000 charge regardless of time used. the rate chart is as follows: $1000 + hourly charge = total =<50 hrs= $15/hr >50hrs but <100hrs= $6.50/hr >100hrs=$4.10/hr ex: so is A1= 200hrs then i want B1 to equal $1820.($1000 + (200 x 4.1))=1820. I have an idea on how to write the formula, but i am having a little bit of trouble with it.
View Replies!
View Related
Time Calculation - Ignore Weekend
Please see attached Excel File. I would like to calculate Column D depending on below criteria. 1. if both date is same than normal Column C-Column B. 2. if both date is not same than time diifrence should not be count after 17:30 to next day 08:30. 3. I do not want to include weekend (SAT & SUN) time diffrence if both date have. In column D I have filled two cells that I want by formula.
View Replies!
View Related
Calculating Time, Timesheet Calculation
I am working on a project involving calculating time. It is a timesheet calculation. I was able to design the following layout: .....A............B..........C..........D.......E.....F 1....Date.........Time IN....Time OUT...Hours... Total 2....01/01/07.....1830.......1930.......01:00...01:00 3....01/02/07.....1930.......2330.......04:00...05:00 4....01/03/07......830.......1900.......10:30...15:30 5 Column A is formatted for DATE. Columns B and C are GENERAL. Columns D and E are DATE format customized as '[hh]:mm' The formula to calculate the time difference between the numbers in column B and C is located in column D. It is as follows: =IF(C4<1000,TIMEVALUE(LEFT(C4,1)&":"&RIGHT(C4,2)),TIMEVALUE(LEFT(C4,2)&":"&RIGHT(C4,2)))-IF(B4<1000,TIMEVALUE(LEFT(B4,1)&":"&RIGHT(B4,2)),TIMEVALUE(LEFT(B4,2)&":"&RIGHT(B4,2)))..................
View Replies!
View Related
Converting Long Time Calculation To UDF
I work with a lot of time based worksheets for a 24/7 operation and constantly use the formula: =IF($E17>$D17,(HOUR($E17)+(MINUTE($E17)/60))-(HOUR($D17)+(MINUTE($D17)/60)),((HOUR($E17)+24)+(MINUTE($E17)/60))-(HOUR($D17)+(MINUTE($D17)/60))) to calculate times worked where $D17 is start time and $E17 is finish time. Shifts can start and finish the same day ($E17>$D17) or go past midnight ($D17>$E17). To make matters even more interesting, there are a number of variables that require adding either "and" and/or "or" conditions to this formula so I can end up with several of these nested and it gets pretty complicated. I'd like to have a UDF so I can just enter the formula: =CalcTime(Start Time,Finish Time)
View Replies!
View Related
Date/Time Calculation For WeekEnds/After Business Hours
What calculation would I enter in a results cell if I wanted to find the delta between 2 times in date format that repersent just the business hours of 8am-5pm, therefore excluding after hours and weekends. eg. Date 1 Date 2 Result Time 23/07/07 8:00 24/07/07 14:55 15:55 So far I can't get the caluclation that will compensate for the after hours and week ends.
View Replies!
View Related
Time Calculation: Calculate How Many Hours Have Elapsed Between To Entries
I am trying to build a spreadsheet to calculate how many hours have elapsed between to entries; start time (H10) e.g. 9:15 AM and end time (I10) e.g. 12:15 PM. The formula that I am using in the calculation cell field (J10) is (I10-H10+(I10<H10))*24. This formula works great till I wish to include in an IF statement. What I would like is if the total hours calculated with the formula (I10-H10+(I10<H10))*24 is less than 4, return 4 (hours) otherwise the value. As well if there is no start time nor end time entered then return zero.
View Replies!
View Related
Vba - Cell Copy And Time Variable
Into Sheet1 there is in A1 (or should i call it better R1C1) a value which is update every sec from i timestamp cell. If the value of this cell change i want to put the previous value into A1(can be another) cell of Sheet2 and keep the new value into A1 of the Sheet1 When the value change again i'd like to put it underneath the cell A1in Sheet2, so that i can take a history of the values. So there will be a column A with different rows. Every 3 mins i want change the column into Sheet2 so from A now the values will be update on column B Everything will start when i open my excel file, because the timestamp will start to run. What about if i want to align the change of the column with a particulary hour? for instance if i open my excel file at 9.33am everything will start since that time, but what if i want make it start from (a multiple of) 9.00 am (so every 3 min from 9am?
View Replies!
View Related
Run-Time Error Setting Worksheet Variable
The user is asked for one piece of information "Enter the ID Number. What the macro should do then is go to Wks1 find the ID Number and change some cells as a result. This bit works. Set Wks2 = Worksheets(strWks) 7 rows from the bottom. My intention was to capture the the name of another worksheet which is held on the same row as the ID Number on Wks1 and call it strWks. Then further down the macro set the value of Wks2 to that of strWks so that the macro will then go to that sheet and remove data from the row with the same ID Number. I get a Time Run Error 9. It just seems to be the bit at the bottom where I am trying to identify Wks2 using strWks. Sub Macro01C_Auto_Resign() Dim Wks1 As Worksheet, Wks2 As Worksheet Dim strFind As String, rngFound As Range Dim lngRow As Long, rngUnion As Range, strWks As String ............................
View Replies!
View Related
Run Time Error 13 Passing Cell Value To Variable
I am having a problem with a run time 13 error on a variable assignment. The variable is being assigned a value from a cell that contains a formula, and I am suspicous that perhaps the mismatch is coming thru this. The de-bugger stops on: Area = ActiveSheet.Cells(count + 41, 8). Here is the complete code (with all my poor coding skills!) Sub Reservoir() Dim Inflow As Single Dim Withdrawal As Single Dim Evap As Single Dim Area As Single Dim Spill As Single Dim Stor_1 As Single Dim Stor_2 As Single Dim count As Integer Dim max As Integer max = ActiveSheet.Cells(9, 4) For count = 1 To max * 12 Stor_1 = ActiveSheet.Cells(count + 40, 11) Inflow = ActiveSheet.Cells(count + 41, 4) Withdrawal = ActiveSheet.Cells(count + 41, 6)..........................
View Replies!
View Related
Display End Time Automatically Upon Entering Start Time And Time Usage
I have a worksheet which contains START TIME in column A, then TIME USAGE in column B and END TIME in column C. User enters start time, followed by the number of time usage in minutes, how could i possibly display the end time automatically in this scenario? how do you add the entered time usage to the start time to display the end time? Say if I enter 1:00 AM at start time and 00:15 minutes on time usage, how can 1:15 AM be displayed on the end time automatically?
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
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
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
String Variable To Call Defined Range Variable
Say you define a public range variable called Inputworksheet and you set it to refer to the worksheet called Inputworksheet. You have a separate string variable with the value Inputworksheet. How do you get this string variable value to call/control the range variable Inputworksheet? I am getting an excel worksheet value from a lookup function that corresponds to the name of a VBA range variable. Once I have this worksheet value, I would like to use the range variable that has the same name as the worksheet value.
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
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
|
TRACKER
