I was using this code in a sheet that contained combo boxes and it worked great. When I redone the sheet a used data validation instead of CB it wold error out.
Code:
Sub savee()
Range("A4:J22").Select
For Each Cell In Selection
If Not Cell.HasFormula Then
Cell.Value = UCase(Cell.Value)
End If
Next Cell
Range("h4").Select
ActiveWorkbook.Save
End Sub
Is there Code for forcing all text in a certain range to Uppercase?
Example: A1:E20
if possible I need the code to be just inserted into the sheet code. Maybe were when the user moves to another cell it changes them then.
I've been trying to modify the following code to find the value "Module Index" in column A. I then need the code to save the row number that the value was found in and copy a range from ("A2:V row number")
I keep getting an "object required" error on the following code line
Set intFindrow = wbTargetBook.Sheets(strName).Range("A:A").Find(What:="Module Index", LookIn:=xlValues)
[Code] .....
I would like a data validation list to only accept an uppercase Y or N from the dropdown in a cell, but users can type a lowercase Ys and Ns and they are accepted. I would like the cell to only accept the uppercase Ys and Ns from the dropdown. Does this require code or is there a simpler way?
View 5 Replies View RelatedI am trying to display a message box for the user if there is "agency" in cell o8 but nothing in p8... I tried the following code but it doesnt work..
If Range("o8").Value = "Agency" And Range("p8").Value = "" Then
MsgBox "Please provide name of agency in cell p8"
Sheet9.Shapes("cross").Visible = True
Else
I have a list generator that creates a set of data in a multi-column & row dataset. I would like a formula to create a list of the alpha data points only which excludes blanks and any errors.
Example:
AADD
#N/A
BB23EEFF
#N/A
#N/A12GG
CC
Results:
AA
BB
CC
DD
EE
FF
GG
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
[Code] ......
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 ?
Using :
MS Excel 2010
IE Explorer 8.0
I'm using Excel 2007.
Is it possible to know which row code is active on whilst it is running?
s/s is 332324 rows and growing. When I run code which goes down one column and takes quite some time I don't know what stage it is at.
I'd like to be able to see on screen which row it is down to.
Function AH02(FIF, xi, di, zi, nt, TAI, ZMT, xi2, nr)
fit1 = Array(0.1352785, 0.02388504, -0.0007874863, -0.008694193, -0.01137302, 0.0004262445, 0.0001408564, 0.001013715, -0.00004032109, 0.006176128, -0.03644328, 0.001557726, 0.0483096, 0.01859556, -0.0008903489, -0.006407128, -0.001693491, 0.00008581065)
fit2 = Array(0.01184698, 0.01976108, -0.0008787826, -0.03195365, -0.01083098, 0.0005161274, 0.004048458, 0.001013387, -0.00005030517, -0.003262156, -0.003314432, 0.0001453592, 0.005082949, 0.001888962, -0.0000865367, -0.0006340434, -0.0001799523, 0.000008493003)
fit = Array(0.1352785, 0.02388504, -0.0007874863, -0.008694193, -0.01137302, 0.0004262445, 0.0001408564, 0.001013715, -0.00004032109, 0.006176128, -0.03644328, 0.001557726, 0.0483096, 0.01859556, -0.0008903489, -0.006407128, -0.001693491, 0.00008581065, 0.01184698, 0.01976108, -0.0008787826, -0.03195365, -0.01083098, 0.0005161274, 0.004048458, 0.001013387, -0.00005030517, -0.003262156, -0.003314432, 0.0001453592, 0.005082949, 0.001888962, -0.0000865367, -0.0006340434, -0.0001799523, 0.000008493003)
ermess = 0
t = TAI
L = 1
If (nr > 40) Then GoTo 900
10 If (nr < 1) Then GoTo 900
11 L = 2
I have the next VBA-
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
The code don't work. What's wrong with it?
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.
View 1 Replies View RelatedI got this code off here and it works really well on my home pc which is using widows 2000 however i need it for work and they are using windows 97.
I get an error with the code in red. I have attached the sheets this refers to, and when i run the code it deletes the dates it matches up with in the master rota sheet which it shouldn't
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 am trying to add code to a module that is already working. however when trying to add new code i keep on getting error messages. It doesnt like the "next i" see attachment.
all else works except for the WEEK1 tab. I am trying to get it so if the "Date Submitted" colum on the MAIN tab is between 5/3/09 - 5/9/09 it will go to tab "WEEK1"
5/10/09 - 5/16/09 to WEEK2
5/17/09 - 5/23/09 to WEEK3
5/24/09 - 5/30/09 to WEEK4
Dim myFileName As String
Dim myworksheet As Variant
myworksheet = ThisWorkbook.ActiveSheet
myFileName = ThisWorkbook.ActiveSheet.Range("h1").Value
ChDir "C:TechnologyComponents"
ActiveWorkbook.saveas Filename:=myFileName
There is only one sheet in this workbook, the sheet name changes each time I run other codes. I am bringing the value of the sheet name into a H1 cell and trying to save with that name.
THe below have written for comboBox selection when I run thourgh form and commandButton code is not working but when run single (Seperately) code will get execute.
Private Sub CommandButton1_Click()
Dim Mydate As Date
Mydate = Date
[Code].....
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.
View 3 Replies View RelatedWhy 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.
why my vba code for sort is not working here. When I do F8 it simply passes the sort code (the one in red) with no action happening. Why.? and do I need to do. Thanks.
Sub Macro1()
'
' Macro1 Macro
'
Sheets(1).Copy Before:=Sheets(1)
ActiveSheet.Rows("1:2").EntireRow.Delete shift:=xlUp
ActiveSheet.Columns("C:M").EntireColumn.Delete shift:=xlToLeft
With ActiveSheet
With Range("A1:C10000")
.Sort key1:=Range("B2"), order1:=xlAscending, Header:=xlGuess,_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End With
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 am trying to import txt files into Excel but instead of inmporting every row, I use the following method to select only the rows I want:
Much ADO About Text Files
However, it mentions a schema needs to be created.
For example:
[File1.txt]
Col1=Name Text
Col2=Age Integer
[File2.txt]
Col1=Name Text
Col2=Age Integer
This is not a problem if I know the names of all the csv files I am importing (I can create one manually) but since I don't know how many files need to be imported, how can I create a schema for it?
Would it be possible to use VBA and create the schema dynamically?
I have VBA code that has the year for embedded within the code for eg "Fixed assets 2007.XLS"
I would to write VBA code that will enable me to change the date from say 2007 to 2008, wherever it appears in a partcular workbook.
Is there a way to change a line of vba code using an input box?
The current macro downloads a file from our intranet. However the filename changes each week. It is only the last portion of the filename that changes since this part is the date it is created. e.g filename010507.xls filename090507.xls
What I want to do is for an input box to pop up, the user then enter the date and this will then replace the existing filename in the code with the new date.
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.
View 4 Replies View RelatedI 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:-
View 4 Replies View RelatedThis code is supposed to clear the contents of row 6 cell if the contents of row 5 cell is blank/cleared. I get an error on the "If Intersect" Line. What's wrong?
View 4 Replies View RelatedFreeze Panes Code not working.
Below is the code im not sure whats up with it?
I have a workbook whose worksheets I want to protect on open, and also want to unprotect by way of a module. The protect code is:
Code:
Private Sub Workbook_Open()
Sheets("Cornealrings").Protect Password:="elohim"
Sheets("Ferrararings").Protect Password:="elohim"
Sheets("Kera Rings").Protect Password:="elohim"
Sheets("IS CALCULATOR").Protect Password:="elohim"
Sheets("IS Manager").Protect Password:="elohim"
End Sub
The unprotect code is:
Code:
Sub Unprotect()
Sheets("Cornealrings").Unprotect Password:="elohim"
Sheets("Ferrararings").Unprotect Password:="elohim"
Sheets("Kera Rings").Unprotect Password:="elohim"
Sheets("IS CALCULATOR").Unprotect Password:="elohim"
Sheets("IS Manager").Unprotect Password:="elohim"
End Sub
The code stops at the first line of the unprotect event, with the message that the password is not correct. If I try to manually unprotect, the password is accepted.
What could make this line of code stop working. It has worked for weeks, and just yesterday it stopped working. :x
ActiveWorkbook.Sheets("Email").UsedRange.AutoFilter Field:=2
The sheet is unprotected, and events are enabled. I don't know of anything different than before. What do I look for that would not allow this code to run?
I am trying to a macro that allows me to change the backgroud color and font color depending of the value of the cell.
For example is cell A1 is having the value between 1 to 3 the background color of the cell will the light turqoise, if the value is between 4 to 20 the cell background color will be green and so on.
I have based on certain posted example and adapt to my code but somehow it is not working.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Select Case Target.Value
Case Is = 1, 2, 3
Target.Interior.ColorIndex = 34
Case Is = 4, 5, 10, 20
Target.Interior.ColorIndex = 43
Case Is = 30, 40, 50