Excel Forum
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Top 15 Excel Tutorials from TeachExcel.com
Instant Access!
E-mail:

Question About Error 2023 Code.

Code:

 
Do
    If ActiveCell.Value = ActiveCell.Offset(2, 0).Value Then
        ActiveCell.Offset(-1, -1).Interior.ColorIndex = 4
        ActiveCell.Offset(5, 0).Select
    Else
        ActiveCell.Offset(5, 0).Select
    End If
Loop Until ActiveCell.Offset(2, 0).Value = "#REF!"


If ActiveCell.Offset(2, 0).Value = "#REF!", then I get an error.

Anyway to make more robust? When I hover over the above statement (ActiveCell.Offset(2, 0).Value), I get an Error 2023 message as a tool tip.

I want the loop to stop if it encounters a "#REF!" statement in the cell as a result of a formula.
TIA!


Free Excel Courses

Getting Started in Excel (Course)

Introduction to Programming Macros in Excel (Course)

Reference Other Excel Files with Formulas and Functions



Similar Excel Tutorials

Wildcards in Excel
Wildcards are characters that allow you to make more robust functions, searches, and filters in Excel. You put wil ...
Custom Error Display
Custom input error message popup window that tells the user what they did wrong. ...
Error bars in Charts in Excel
How to add, manage, and remove error bars in charts in Excel. Error bars allow you to show the potential variance ...
Add Error Display and Macro Functionality to the Form
Make the Form validation work by adding error count, display, and Submit button macro functionality. ...


Helpful Excel Macros

Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Delete All Chart Sheets in Excel - Only Chart Sheets are Deleted - Not Embedded Charts
- Delete all chart sheets and tabs in Excel using this macro. This will only delete the charts and graphs that are in the
Highlight Cells which Contain Formulas
- This macro will highlight all of the cells in a worksheet which contain a formula. The first one listed will highlight
Highlight Cells with Text or Formulas (non-empty cells)
- This macro will highlight all cells in excel which are not empty. This means that if a cell contains formulas, text, nu
Pop-Up Message Box When a Cell Reaches a Certain Value or Contains Certain Text
- This macro will display a message box in excel when a cell reaches a certain value or contains certain text. This means

Similar Topics







Hi there,

I am currently using 'Activecell.Offset(1,0).Select' to move down one cell at a time when I click on a button.

The problem I have now is that if someone was to filter by something then the 'next cell down' could be hidden behind the filter (by that I mean it didn't meet the filter criteria).

Is there anyway to move down to the next row, even if that row does not follow on Sequentially .

Any help would be greatly appreciated.

Regards,

James


Hi there! This is definately a quick question, but I need to select a range. I'm looking to do so along these lines:
Code:

Range("Activecell.End(xlDown)", "Active.End(xlToRight)").Select


This is definately a problem of not knowing the right jargon to do so.

Could someone please assist?

Thanks in advance!
final


Hello,

As the title goes this is as simple as it gets. The macro worked good when I was using excel 2003 but now that I have 2007 everything works for except the resizing (Picture.Width & Picture. Height).

Code:

Sub AddImage()
Dim strPath As String
Dim strFile As String
Dim Picture As Object
Dim Response As String
Dim C As Range
strPath = "C:\\Photos\"
 
For Each C In Range("A1", Range("A65536").End(xlUp))
    If C  0 Then
        With C.Offset(0, 1)
            Set Picture = Nothing
            On Error Resume Next
            Set Picture = Sheets("Raw Pix").Pictures.Insert(strPath & C.Value & ".jpg")
            Picture.Top = .Top
            Picture.Left = .Left
            Picture.LockAspectRatio = msoTrue
            Picture.Width = 157
            Picture.Height = 138

        
    End With
    End If
     
NextC:
Next C
End Sub


I have tried to record some macros while I am resizing the pictures but to no avail the macros are blank.

Please help

Thanks!


Is there an on error exit sub command. I would like my Macro to just stop running if there is an error instead of an error message popping up. Thanks in advance


Hi,

I have the following code to put data from a VBA userform into Excel

Dim Sh As Worksheet
Dim Rng As Range
Set Sh = ActiveSheet
Set Rng = Sh.Range("A65536").End(xlUp).Offset(1, 0)
With Rng
.Cells(1, 1) = Surname.Text
.Cells(1, 2) = forename.Text
.Cells(1, 3) = datein.Text
.Cells(1, 4) = origin.Text
.Cells(1, 5) = Addressee.Value
.Cells(1, 6) = usual.Value
.Cells(1, 7) = dateto.Text
.Cells(1, 8) = permission.Value
.Cells(1, 9) = dateseen.Text
.Cells(1, 10) = requestview.Value
.Cells(1, 11) = Invoice.Value
.Cells(1, 12) = notes.Text
.Cells(1, 13) = datecompleted.Text
.Cells(1, 14) = holdsend.Value
.Cells(1, 15) = fee.Text
.Cells(1, 16) = notes2.Text
.Cells(1, 17) = dateseen.Text
.Cells(1, 18) = invoicesent.Text
.Cells(1, 19) = Paid.Text
.Cells(1, 20) = Complete.Value

End With

What I want to do next is click on the surname on the speadsheet and call up the userform with the fields complete for that person. I would also like to be able to edit/update the form and update the information on the speadsheet accordingly.

Any help would be greatly appreciated!

Many thanks!


Hi,

I am using

ActiveSheet.ShowAllData

in a piece of VBA and it causes an error if the sheet does not have a filter applied! Is there an if statement I can use to check if the data is filtered?

Thanks - this site is an absolute life saver!

Rich


I have a large spreadsheet in Excel 2007. I am converting it to a .csv file to import in to another program. I need to delete all commas from all data.

When I try to replace all commas (with nothing or with another character), I get the error message "The formula you typed contains an error."

I have tried various formats (text, general, etc.) and various file types (.xls,.xlsx, .csv) and still get the same error.


How can i solve a debugger error that states "run-time error '9'...Subscript out of range.

for a search box code provided by Joe Was




I did a search in here but didn't find an answer so here goes nothing...

I have a spreadsheet with data from A1 to H1 down to A275 to H275. I select all of it, Copy, go into the Sheet2 tab and select A1, Paste Special -> Transpose and it give me the following error message:
Quote:

The information cannot be pasted because the Copy area and the paste area are not the same size and shape. Try one of the following:
- Click a single cell, and then paste.
- Select a rectangle that's the same size and shape, and then paste.

For the life of me, I can't figure out why it's not working! I have done this many times with other spreadsheets. Has anyone ever experienced this? If so, were you able to resolve it and how?

Many Thanks,
Mike


I'm using some basic code below in an on Workbook Open event to format cells with a value less then 2 and less than 1 with a particular color.

The code works, but it really slows my worksheet down when opening. Is there better way to write this? Thanks!

Code:

 
Dim myRange As Range
Dim cell As Range
Set myRange = Range("V6:V50000")
    For Each cell In myRange
    If cell.Value < 2 Then cell.Font.ColorIndex = 5
    If cell.Value < 1 Then cell.Font.ColorIndex = 3
    Next





I am trying to write a formula that uses both an if statement and a vlookup statement.

It's for a house cleaning price list, with two sets of prices. I want the formula to say if the value in E7 is R, i want to lookup the value for the house in the second column. If the value in the cell is anything else, i want to lookup the value for the house in the third column.

So in sense, my formula should look like =IF (E7=1,"=VLOOKUP(A10,'Clean Prices'!$A$2:$C$58,2,FALSE)","=VLOOKUP(A10,'Clean Prices'!$A$2:$C$58,3,FALSE)")

But it doesn't work. Is it possible to nest a vlookup in an if statement?


Hi all,

I have written some code that when a button is selected will Refresh a Data Query Table and all of it's information. However, I seem to be getting issues, can some one point me in the right direction with the code?

Code:

 
.Sheets("Sheet 1").ListObject.QueryTable.Refresh BackgroundQuery:=False


When I recorded myslef doing this process it looked like this:

Code:

 
Sheets("Sheet 1").Select
Range("D70872").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False


Many thanks!


I am trying to insert a range of cell in the body of an outlook email with the same format. The code I am using now is below and it does insert anything in the email body.

Code:

Sub Mail()
Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
        .To = "cbelcher@jpfryelaw.com"
        .CC = ""
        .BCC = ""
        .Subject = "Burden Report"
        .Body = ActiveSheet.Range("A1:D12")
        .Attachments.Add ("C:\Documents and   Settings\cbelcher\Desktop\Burden Report.xls")
End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
    Set objMsg = Nothing
End Sub





I found this solution for "drop down list with hyperlink" but it did not work.

Perhaps a better solution is to use a workaround that relies on the HYPERLINK function to refer to whatever is selected in the drop-down list. For instance, if you have your data validation drop-down list in cell A1, then you might put the following formula in cell B1:

=HYPERLINK(A1, "Goto Link")

The solution directly above provides exactly what I am looking for
in the field where I write the formula, but it fails to hyperlink.
I have created a drop down list and linked each one of them to a
specific worksheet. When I select them individually they link to
appropriate worksheet. But when I select them in the drop down
list I receive the following error when I select the Hyperlink in
cell B1 as directed above.

"Cannot open the specified file"

Any thoughts?

Bob


Still learning VBA - I am trying to delete an entire row based on a condition in one cell in the row. Typically I would just filter on that value and delete the rows, but I am not sure if that is a possibility in VBA code. Can you provide the code if not too complex.

Select Cell A1 if value is 100 delete entire row, else skip to next row. Then loop through each row in the spreadsheet till all rows with selected cell equal to 100 are deleted.


Hello,

i've got the following problem:

I want users to double-click on a row on a protected sheet and then do some code based on the row-number of the clicked cell. I've protected the sheet because it contains a lot of formula's.

When a user double-clicks a row it triggers the code through the Workbook_SheetBeforeDoubleClick event.
After the code is executed Excel shows a message that the cell that was clicked was protected etc etc.

How can I prevent this message from popping up?

I've already tried
Code:

application.displaywarnings = false


but that didn't work

Thanks


I am looking for the code to select the visible data after applying a data filter. Actually I know how to select the data after applying the data filter but the issue is I am not able to exclude the header row and give the target range as used (non-blank) rows only!!

I am using below code to Select the Visible rows in the target range:

Code:

Range("A:p").SpecialCells(xlCellTypeVisible).Select


Problems in this code a

1) after applying the filter, while selecting the data it is selecting all the rows in given range till last row on the workbook. I need this to select the the data only till the last used row in the given range.

2) It is not possible to provide the address of the first row after we apply the filter since the first row address may change depending on the values in the table.

E.g. 1st time when I am running the macro the first row in the visible filtered data is starting at Cell address A4 and next time when I will run the macro it may be A6

3) The Code is also selecting the 1st row which is a header row. How can we exclude it from selection.

Some one please revert with the solution.
Thanks in advance.


I'm trying to use VBA to go to a website that requires a User Name, Password, and a Submit Button.

So far I can get everything to work besides the Submit part. The code runs without errors, but doesn't actually "hit" the submit button on the webpage.

For posting, I removed my actual user name and password and and used the generic " User Name " and " Password " highlighted in blue.

I highlighted another section in green that I took from a previous post hoping it would solve my problem. The link is: http://www.mrexcel.com/forum/showthr...xplorer+submit


Here is the code I am using:

Sub GoToWebSiteAndPlayAroundNew()

Dim appIE As Object ' InternetExplorer.Application
Dim URL As String


Set appIE = CreateObject("InternetExplorer.Application")
URL = " https://efolio.morgankeegan.com/escripts/defaultLogon.asp?errCode=2 "


With appIE
.navigate URL
.Visible = True

Do While .busy: DoEvents: Loop
Do While .ReadyState 4: DoEvents: Loop

.document.getelementbyid("fUserName").Value = " UserName "
.document.getelementbyid("fPassword").Value = " Password "

End With


On Error Resume Next
x = 0
For Each mitem In IE.document.all
mitem.Value = "x"
x = x + 1
Next

x = 0
For Each mitem In IE.document.all
If x = "Submit" Then
mitem.Click
Exit For
End If

Next

End Sub


Corporate edict.

I have a worksheet that is locked and protected now, except for cells in a certain collumn. I have named the cells in that column "MS96A".

If a user enters a date in a cell or range of cells anywhere in the column, the changed cells also need to be locked and protected (Once they enter a date, it is not allowed EVER to be changed again. Corporate requirement! *Shrug*).

What I am looking for is this. If the user selects that cell again, they will get the usual pop-up message, "The cell or chart that you are trying to change is protected..."

I think I am close, but I am getting an "End If without block If" error on the If Clause.


Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim MRange As Range
Set MRange = Range("MS96A")
' If Not Intersect(Target, MRange) Is Nothing Then For Each cell In MRange Sheets("Sheet1").Unprotect Password:="temp"
cell.Interior.ColorIndex = 3
cell.Font.Color = vbBlack
Selection.Locked = True
Selection.FormulaHidden = False
Next cell
ActiveSheet.Protect Password:="temp", _
DrawingObjects:=False, _
Contents:=True, _
Scenarios:=False
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub


Hello all,

Until now I have been able to find all my answers through searches. As a VBA novice, it has been very helpful. I am stumped on this one, however. I am trying to autofill from the selected cell in Column C down. I would like it to stop at the last cell with data in Column B. This is the code I have so far:

Range("C2").Select
Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown))

This does the autofill, but doesn't stop at the last cell with data in Column B.

In the past I have use this code to acheive similar results:

Dim endRow As Long
endRow = Cells(Rows.Count, "B").End(xlUp).Row
Range("C2").AutoFill Destination:=Range("C2:C" & endRow)

The problem with this code is that I will not always be starting in "C2". I need code that uses whatever the selected cell is.

All help is appreciated. Thanks!


I'm trying to open a pdf file from within excel vba. I have tried using the followhyperlink method but adobe acrobat opens very briefly then immediately closes Code:

Sub OpenPDF()

'Dim pdf As String

    On Error Resume Next

    'pdf file to open
    pdf = "K:\PDF\mypdf.pdf"

    'open the pdf file
    ActiveWorkbook.FollowHyperlink pdf

End Sub


So then I tried to create an instance of acrobat by setting a reference to the acrobat object but I can't get this to work either!

The code I'm using is Code:

Sub OpenPDF()

    Dim pdf As AcroPDDoc
    Dim strPDF As String

    Set pdf = CreateObject("AcroExch.PDDoc")
    
    'pdf file to open
    strPDF = "K:\PDF\mypdf.pdf"

    'open the pdf file
    pdf.Open strPDF

End Sub


Any ideas what could be wrong with either approach?

Thank-you


I am trying to figure out how to write a formula to figure out production cycle time.
We will be building 8 "widgets" a day.
We will work 10 hours a day.
There is a 20 minute break at 9:20 am.
Production shuts down for lunch 30 minutes for lunch at 12:30.
Production starts at 6:00 am.
Here is what I can do.
Production cycle time = (10*60)-30/8 or 71.25 minutes
If we start at 6:00 am, the first scheduled cycle completion time = TIME(6,0,0+71.25/1440)

My problem is when I am trying to use a logical "IF' statement to account for the 20 minute break or lunch and still calculate the end of each cycle time through the day I receive a number of error messages.
This is the formula I am trying to make work. I have the cell the formula is in, formatted with a "mm,ss" format.
=(IF(D4+G1/1440>9:20,(D4+G1)/24,(D4+G1+15)/1440))
I have included an attachment to help (a picture is worth a thousand words).

Any help will be greatly appreciated.
I have bought a number of books in an attempt to figure this out, and I am still stumped


Hi there,

I have a piece of code called ConvertDates that formats data contained on 6 worksheets. The 6 data sheets all contain a data connection to a website of foreign exchange tables.

What I want is for my code to execute as soon as the data connection refresh has finished.

When I use the statement

Code:

ActiveWorkbook.RefreshAll
Application.Run "Project1.xlsm!ConvertDates"


The code executes the macro whilst the refresh is still happening, thereby screwing up my results. I don't really want to use a timed wait, because the refresh speed is going to vary from user to user.

Is there some way I can tell excel to wait till the refresh has finished and then execute the code?

Any help would be hugely appreciated.


I am looking for assistance in having one cell in a text format equals another cell that contains a time value in hh:mm format.

For example: Cell A1 has a time format (hh:mm) value of 04:00; which is the Start Time. I would like cell D1 to have a text format value of "04:00" (result is dependant upon what is entered in A1). I would duplicate the same formulas to reflect Stop Times in other cells.

My final result is to have another cell (F1) use the Concatenate formula to have the Start and Stop time shown in one cell as "04:00 - 12:30". The times would change based on the Time formated values entered into the Start and Stop time cells.

I have researched this in the board and found many excellent ways to do the opposite, but not convert Time format to Text format. Any assistance is greatly appreciated.

-Shane


I often use if statements to return empty cells, for example:

=IF(a1=0,"ERROR","")

The trailing "" returns an empty cell. The problem is, it is not TRULY empty. If I fill that formula down, I cannot, for example, jump from one "ERROR" cell to the next by hitting Ctrl arrow-down because it seems to think that these empty cells have contents.

Is there a way to designate, in such a formula as above, to return a TRULY empty cell?

Thanks