VBA Code Not Working As Intended

Nov 6, 2009

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.

View 9 Replies


ADVERTISEMENT

Worksheet Copy And Rename Not Working As Intended

Oct 11, 2009

I've got a Workbook that contains the following worksheets:

1. conversion(2) - Hidden
2. Old Data - Hidden
3. Blank Form

I want to copy "Blank Form" once for each day of the month.
I'd like to rename each copied worksheet with the Date (i.e. Oct 01 2009)

Here's what I'm working with:

Sub Copysheets ()

Worksheets("Blank Form").select

Dim x As Integer

For x = 1 to 30

Worksheets(1).Copy after: =Worksheets(x)
Worksheets(x+1).Name=Format(DateSerial(2009, 20, x), "MMM-DD-YYYY")

Next x

End Sub

The problem I'm having is that the first sheet to be copied is the "Old Data" worksheet and the copy is renamed Oct-01-2009. After that the correct Worksheet is copied and renamed Oct-02-2009 and so on.

I can't figure out why it's copying the "Old Data" worksheet first.

View 9 Replies View Related

For Loop Not Running As Intended, Can't Find

Feb 17, 2010

I thought my code was working properly, but then I tried different numbers for myNum and I realize it's not. Here's the

View 5 Replies View Related

VLookup To Pull A Value Out Of A Range And Return Intended Data

Jan 25, 2010

I am trying to use a VLookup to pull a value out of a range and return my intended data. See attached spreadsheet. The data I'm pulling from is in the top chart. The bottom chart, in the factor column, is where I want the data to pull to. Ex) If the "Months of Development" column in the bottom chart =13, I want it to pull the factor value from the top chart where the month is 13. The problem is that the top chart has a range of values in the "Month Range" column of 13 - 24. I don't know how to get the VLookup to recognize this and pull the same factor into the chart below whether the value is 13 or 24. Maybe VLookup isn't the best option here.

View 2 Replies View Related

Excel 2010 :: VBA Code To Extract HTML Source Code Not Working On Google Sites

Dec 6, 2012

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

View 1 Replies View Related

What Row Is Code Working On

Nov 27, 2008

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.

View 14 Replies View Related

Code Is Not Working

Apr 24, 2007

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

View 9 Replies View Related

VBA-code Not Working

Feb 10, 2009

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?

View 9 Replies View Related

VBA Add Request Code Is Not Working

Jun 18, 2014

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 Related

Code Not Working On Windows97

Nov 14, 2008

I 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

View 4 Replies View Related

Code Not Working As Planned

Apr 14, 2009

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.

View 2 Replies View Related

Add Code To A Module That Is Already Working

May 12, 2009

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

View 4 Replies View Related

'Save As' Code Not Working

Oct 29, 2009

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.

View 14 Replies View Related

Combobox Code Is Not Working

Nov 17, 2011

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].....

View 1 Replies View Related

VBA Code Not Working On Another Computer?

May 22, 2013

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 Related

Code For SORT Is Not Working

Jan 20, 2010

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

View 9 Replies View Related

HPageBreak Code Not Working

Jan 18, 2006

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...

View 9 Replies View Related

Unprotected Sheet Code Not Working?

Jun 9, 2014

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 Related

VBA Code Not Working When I Protect The Sheet

Nov 9, 2008

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:-

View 4 Replies View Related

Clear Contents Code Not Working

Sep 15, 2009

This 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 Related

Freeze Panes Code Not Working.

Oct 27, 2009

Freeze Panes Code not working.
Below is the code im not sure whats up with it?

View 3 Replies View Related

Code To Unlock Password Not Working

Apr 4, 2014

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.

View 7 Replies View Related

Autofilter Code Stopped Working

Feb 20, 2007

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?

View 9 Replies View Related

Color Cell - Code Not Working

Apr 25, 2007

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

View 9 Replies View Related

ReDim Vs. Dim: Code On Another Machine Not Working

Oct 19, 2007

I have: ReDim X(3,1) as double. The code runs fine on my machine, but when I take it to another machine, the code gets stuck on that line.

View 3 Replies View Related

Event Triggered Code Not Working ?

Mar 20, 2008

I have a manually calc'd workbook with the following code


Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("VAL1CELL")) Is Nothing Then Me.Calculate
If Not Intersect(Target, Me.Range("VAL2CELL")) Is Nothing Then Me.Calculate
If Not Intersect(Target, Me.Range("CHOICE")) Is Nothing Then Me.Calculate
If Not Intersect(Target, Me.Range("$L$36")) Is Nothing Then Me.Calculate

If ActiveCell.Address = "VAL1CELL" Then Range("VAL2CELL") = Range("Y$41")

End Sub


Everything works as it should other then the part that is

If ActiveCell.Address = "VAL1CELL" Then Range("VAL2CELL") = Range("Y$41")

When the user selects VAL1CELL This is cell B2 and is a drop down, I want VAL2CELL which is C2 and also a drop down to show what is in Y41 (i.e the first name that appears in the drop down...not a thing happens ? is there a flaw to my code ?

View 9 Replies View Related

Code Stops Working After X Rows

Nov 23, 2008

I'm using Excel 2007 and my s/s is 360000 rows deep.
To cut+paste formulas+formats from one column to another I'm using the following
Sub move_formula_and_formats_from_I_to_L()

Dim cell As Range

Application.ScreenUpdating = False

For Each cell In Range("I1", Cells(Rows.Count, "I").End(xlUp))
With cell
If .HasFormula And Not .Offset(1).HasFormula Then
.Cut Destination:=.Offset(1, 3)
End If
End With
Next cell

Application.ScreenUpdating = True

End Sub
The code stops working after 159000 rows and highlights (in yellow) the line:
.Cut Destination:=.Offset(1, 3)

View 9 Replies View Related

Code Not Working With Merged Cells

Jun 10, 2009

i have the following code that works ok with single cells but wont work with merged cells

what is supposed to happen is the row is highlighted when double clicked anywhere between column 4-52 and reverts back if double clicked again

But this will not work if the cells are merged.

Also can the colours be changed to RGB colours and not standard colours

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Count > 1 Then Exit Sub
If Cells(Target.Row, 4).Interior.ColorIndex = xlNone Then
Range(Cells(Target.Row, 4), Cells(Target.Row, 52)).Interior.ColorIndex = 4
Cancel = True
Else
Range(Cells(Target.Row, 4), Cells(Target.Row, 52)).Interior.ColorIndex = xlNone
Cancel = True
End If
End Sub

View 9 Replies View Related

Code Not Working If No Selection In Listbox

Jun 16, 2006

Why this code doesn´t enter in the if condition when i don't select any item from the listbox

semana = ListBox1.Value
If semana = Null Then
MsgBox ("Need to choose one item!!!")
Else
emd = Range("A48").Value
End If

View 3 Replies View Related

Selection.Replace Code Not Working

Jul 14, 2006

This one has really got me stumped:

Sub NCR()
Application.DisplayAlerts = False
Workbooks.Open ("J:AcctMgt3NWFMScriptingScriptsDailyReportsNCRNCRvdn.xls")
Sheets("NCRvdn").Columns("B:B").EntireColumn.AutoFit
dRow = Sheets("NCRvdn"). Range("B1").Text
dMonthDay = Format(dRow, "mmdd")
dDay = Format(dRow, "dd")
dPrevDay = dDay - 1
dMonthName = Format(dRow, "mmmm")
dMonthNum = Format(dRow, "mm")
dYear = Format(dRow, "yy")
dPrevDay2 = dMonthNum & dPrevDay.............

This opens the first file and dRow="7/13/2006". The next file that it opens contains links to information from the previous days. Without VBA you just drag the previous day down, select the row of data and do a replace all, say from 0712 to 0713. As you can see I even tried to make it use the specific data I wanted versus the variables; still doesn't work. What really gets me is that if I go back to the sheet after this code runs, I go to EditReplace, replace all "0712" to "0713" and it does it. It has to be the code then right??

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved