Every time I try to add the contents from the request form to the Master CCO tab, the information does not dump. The only way the form will close is if i hit the cancel button. I don't know what I've done wrong with the add request code.
I have the following code which works but is slow. All the code is the same except the address part and the destination range. Any help simplifying would be great. Also, every time I run the sub it asks me if I want to replace the destination cells. Is there any code to circumvent that? Lastly, although the second and third query requests have a different destination range, excel shifts the previous query to the right, hence first command after final query request.
The below code uses data in column "A" (Const cl& = 1) to create a sheet for every unique value in column "A".
I would like for the code to request what column to use as source data. As an example when the code first runs a pop-up box would ask for a column letter to use as the source, after entering the letter (or corresponding column number) the code will execute.
Code: Sub Add_sheets_from_A() Range("XA1") = ActiveSheet.Name ActiveSheet.Name = ("Add_Sheets")
I have previously used the following code to successfully pull out IE webpage source code for string manipulation.
Its a crude example to demonstrate the principle:
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) Public IE As Object Sub Sample() Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True
However when I substitute in a Google websites address into the IE.Navigate command, the code runs to the "Source_Code = IE.document ...." line then flags up a Microsoft Visual Basic error. "Run-time error '438': Object doesn't support this property or method"
The webpage that I am trying to access is a confidential company site, so you won't be able to access it yourself, but starts with [URL] ......
The one thing that I have noticed about this website is the Privacy Report icon in the lower right status window (Picture of an eye with a restricted symbol in front). I don't know whether this is the cause of my problem, or purely an incidental observation.
Is there something peculiar with Google sites that means that the source code cannot be extracted in general, or is this an issue specific to my site ? Does the Privacy Report icon have any relevance, and if so how do I switch that off ?
I have Time Data in one column, and Values in the next, and then a blank column (for calculations, like subtraction/time).
Is there any way to automate the =Cell2-Cell1 command for regions of blue cells and have it paste the value in the blank column lined up with the first blue cell of the region?
The attachment will make more sense, but this is what I'm talking about:
Before subtraction macro:
BlueTime1 Value Blank BlueTime4 Value Blank BlueTime2 Value Blank BlueTime5 Value Blank BlueTime3 Value Blank BlueTime6 Value Blank Blank Blank Blank Blank Blank Blank BlueTime7 Value Blank BlueTime10 Value Blank BlueTime8 Value Blank BlueTime11 Value Blank BlueTime9 Value Blank BlueTime12 Value Blank
After subtraction macro:
BlueTime1 Value BlueTime3-BT1 BlueTime4 Value BlueTime6-BT4 BlueTime2 Value Blank BlueTime5 Value Blank BlueTime3 Value Blank BlueTime6 Value Blank Blank Blank Blank Blank Blank Blank BlueTime7 Value BlueTime9-BT7 BlueTime10 Value BlueTime12-BT10 BlueTime8 Value Blank BlueTime11 Value Blank BlueTime9 Value Blank BlueTime12 Value Blank
I have a sheet with the data in the following way(its just a sample).................
now where ever i have ave i need to get the average of the range below it. Example: for the first ave i should calculate ave of new two rows cause the next row would be the ave of next range. I have close to 15000 rows and 135 columns of data like this.
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("E:N")) Is Nothing Then If Range("E34").Value = 0 Then Application.EnableEvents = False Range("G8").Value = Range("G8").Value + 1 If Range("J34").Value = 0 Then Application.EnableEvents = False Range("G9").Value = Range("G9").Value + 1 Application.EnableEvents = True End If End If End If End Sub
I facing a problem to generate request id number. Actually i need to generate id like example "RQ1013-01" where "RQ" is constant word, "1013" is month and year while "-01"is generated number. and every month i want the id number start from -00 back. thus in a month there is only 99 request is available.
I'm trying to find a formula for calculating the number of business hours a service request may be open; this request may have been opened and even closed outside of business hours. I found a formula that appears to be working:
Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If IsNumeric(Target.Value) Then 'Clear the Invalid entry Range("L4").ClearContents Range("L5").ClearContents
If Target.Address = "$K$4" Then Range("K5").ClearContents ElseIf Target.Address = "$K$5" Then Range("K4").ClearContents End If Else Range(Target.Address).ClearContents Target.Next = "Invalid Input" End If..........
However for L28, L29, L48, and L49 the invalid input entry is not showing up properly.
I have made a program using excel vba with userforms, however upon using it on another or any computer with the same version of Office(2007) i am getting a debugging error and highlighting any lines with the words "format" & "date" in the vba code.
Why is the following code not running the macro the number of times I type into the box?
Sub Macro10() 'Sub RunABunch() Dim ans As Variant, i As Long ans = Application.InputBox("Enter a Number of loops", Type:=1) If Not IsNumeric(ans) Then ' user hit cancel, so exit sub Exit Sub End If For i = 1 To CLng(ans) ' do your work here Next i ' Macro10 Macro ' ' Cells.Find(What:="Chief", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate Selection.Cut ActiveCell.Offset(0, 2).Range("A1").Select ActiveSheet.Paste End Sub
I didnt write the loop and input portion of the code, I just recorded the macro and added that bit of code from the net.
In the EXCEL helpfile, there is an example code under HPageBreaks Property:
For Each pb In Worksheets(1).HPageBreaks If pb.Extent = xlPageBreakFull Then cFull = cFull + 1 Else cPartial = cPartial + 1 End If Next MsgBox cFull & " full-screen page breaks, " & cPartial & _ " print-area page breaks"
However, I can't get the code to run. I am consistently getting an error :
Run Time Error "9" : Subscript out of range on very first line of the code. How can one get a subscript out of range with a "For each ... Next" statement ?!!
I cite this as an example, since I am unable to create workable code using HPageBreaks, which I need to...
I'm currently using a vba script to unprotect all of my worksheets within my workbook in order to refresh the pivots and then protect them again once the pivots are completed, but there are two worksheets I would like to remain unprotected in order for users to be able to edit them after the pivots have been refreshed. Now I've tried adding a sub code in order to unprotect one of the sheets as a test to see if it will allow me to edit the worksheet after I update the pivots. Unfortunately the coding isn't working, and the worksheet is still remaining in protect mode. Here's the code I am using to protect the worksheets when refreshing the pivot as well as the code I am attempting to use in order to unprotect the one worksheet.
I have used the below code to make hide/unhide some columns in my excel sheet. It works fine. Now, I need to protrect the sheet except the coloured cells. So I unlocked the cell by clicking the property of the colored cells & then did this :- tools- protection-Protect sheet - protect unlocked cells. But after protecting this sheet the macro code of hiding/unhiding of the columns are NOT WORKING & Errors is coming. I have used the below code:-