Free Microsoft Excel 2013 Quick Reference

Word VBA: run-time error 75

This code renames Word document: first, detect path, then name and extension, close document and renames it, and open again.
Code is stored in Normal.dotx.

...but - when file is locally stored (hard disk) i have "run-time error 75", after this i click "Debug" and "Continue" and everything is OK. When file i stored on network disk - there is no error message??!!

	VB:
	
 
Sub RenameCurrentFile() 
    Dim wdApp As Object 
    On Error Goto 0 
     'OBAVEZNO: staviti ovo u Normal.dot!
    Application.DisplayAlerts = False 
    staroime = ActiveDocument.FullName 
    samofolder = ActiveDocument.Path 
    If (Right(Left(staroime, Len(staroime) - 4), 1))  "." Then 
        novoime = Left(staroime, Len(staroime) - 4) 
        ekst = 4 
    Else 
        novoime = Left(staroime, Len(staroime) - 5) 
        ekst = 5 
    End If 
    ekstenzija = Right(staroime, ekst) 
     
    novo = novoime & "_INTERNAL" & ekstenzija 
    ActiveWindow.Close 
     
     'here the error begins!
    Name staroime As novo 'rename
    Application.DisplayAlerts = True 
    Documents.Open(novo).Activate 'open again
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Additionally:
- i have all user access permissions
- file is not Read-Only
tnx


Hi All,

I'm have created many excel reports and the same is being used by various users in my company. I need to track the usage of these reports. Therefore, I created a file called "log.inf" in a network drive. In the excel reports, the workbook open event opens the text file (log.inf) and updates the info needed to be tracked. This works perfectly fine on my system (Excel 2007/ Windows Vista) and some of the users systems. However, some users (Excel 2010/ Windows 7) get the run time Error 75 - Path/ File Access Error. However, i have ensured that the users definitely have access to the network drive and the specific "log.inf" file. Below is the Code I'm using:


	VB:
	
 Workbook_Open() 
    Dim ff As Long 
     
     
    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 
     
    On Error Goto netcon 
    ff = FreeFile() 
    Open "fileshares.corpgroupsRAPID_REPORTSlogslog.inf" For Append As #ff 
    Print #ff, ThisWorkbook.Sheets("AI_O").Range("A2").Value & ";" & Environ("USERDOMAIN") & ";" & Environ("USERNAME") & ";"
& Now 
    Close #ff 
     
    Exit Sub 
     
netcon: 
    MsgBox "You are currently not connected to the network. Please connect to the network and reopen the file." 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Also, I have tried keeping an excel file in logs folder and tried updating the excel file for those users who were getting error. This works fine, ruling out the possibility of Network Permission issue.

Any help will be greatly appreciated.

Thanks in advance,
Saiju Paul C

Dear All,

Please can somebody throw some light on a problem i am having? I've been trying to solve it now for the last day and its driving me bonkers!!

Basically i have added a simple userform to a project, which contains nothing more than a ComboBox. The comboBox source is a range in the Excel spreadsheet.

Whilst in the VBE, everything runs fine. However, when i save the spreadsheet, Excel invariably crashes with the usual message "Excel encountered a problem etc, etc" or if it doesn't, as soon as i re-load the spreadsheet, I get the following string of error messages:-

Path/File access error
Run Time error 75
Cannot find specified object,
catastrophic failure,
Object was unloaded

Pressing debug shows the cursor sitting on the line "userform1.show"

It sounds like for whatever reason, the userform stuff is not being saved but i have checked and checked that there is enough disk space and i have write access rights etc and all is well. Besides, the problem seems intermittent.

This is the first time i have added a combobox to a userform so i'm guessing (hoping) that its something to do with this? Do i need to do anything "special" to initialise this i.e allocate memory when the user form loads?

I am appreciate any help or guidance given.

Many thanks

Pete

PS I have "cleaned" my project so i don't think its anything lurking deep in old code etc and tried it on another computer with the same result!

Word 2007 - Run-time error '6124'

We have files with macros configured... the users with administrator access can open the doc, enable macros, put a code received from sender of protected doc, and file decript and open the file normally...

on a comon user, without admin rights, when the file is open, when the user select to open the window for enable macro, the error appear:

Microsoft Visual Basic

Run-time error '6124': You are not allowed to edit this selection because it is protected.

We need the users can open this protected file type whitout problems, what is the procedure for that?

Thanks.leosvi

Hi,
I have a routine that copies a worksheet, opens the SaveAs dialog with a File name made up of values from named ranges and a date from the copied sheet. It also attempts to make a new folder appended to the current workbook path. Here's where I'm having trouble. I keep getting the run-time error 75. The problem is that the error is intermittent. I have tried moving the lines around thinking somehow the MkDir was picking up the newly copied sheet's path instead of the original workbook path, but no joy.

Here is the code:

Option Explicit

Private Sub cmdCopySaveAs_Click()
Dim c As Range
Dim d As Range
Dim NewSht As Worksheet
Dim rngDI As Date
Dim Fname As Variant
Dim str1 As Variant
Dim str2 As Variant
Dim str3 As Variant

str1 = Sheets("Proposal").Range("Lang1").Value
str2 = Sheets("Proposal").Range("Lang2").Value
str3 = Sheets("Proposal").Range("Lang3").Value
rngDI = Sheets("Proposal").Range("PropDate").Value
Fname = Sheets("Set-Up").Range("Project_Name").Value _
& " " & Sheets("Set-Up").Range("Contractor_s_Name").Value _
& Format(rngDI, " mm-dd-yyyy ")
MkDir ThisWorkbook.Path & "Proposals" <<<<ERROR
On Error GoTo 0
Sheets("Proposal").Copy

Application.Dialogs(xlDialogSaveAs).Show ThisWorkbook.Path _
& "Proposals" & Fname & ".xls"
Set NewSht = ActiveSheet
On Error Resume Next

Application.ScreenUpdating = False
Application.EnableEvents = False
With ActiveSheet
.Shapes("cmdCopySaveAs").Delete
.Shapes("cmdPickScopes").Delete
.Range("Lang1") = str1
.Range("Lang2") = str2
.Range("Lang3") = str3
Set d = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
For Each c In d
With c
.Value = .Value
End With
Next c
End With
ActiveWorkbook.Save
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

I have an excel sheet which is working on my friends machine but gives "Excel VBA Run-time error '13' Type mismatch" on my machine.
Its coming while running a macro for a file and same I tried to sent to other colleague and its working fine on his machine. This was just to check if some how file got corrupted. I'm allways getting the following error: Excel VBA Run-time error '13' Type mismatch

I didn't change anything in the macro so dont know why am I gueting the error.

Please help me in finding a workaround or a possible solution for the same.

Hi,
I have designed a user-form from within the MS VB module of Excel. I have set up the form as required adjusted all the properties as required and have used the following vba code to make it work;


	VB:
	
 CmdOk_Click() 
    ActiveWorkbook.Sheets("sheet2").Activate 
    Range("b2").Select 
     
     'to get down to the 1st empty row
     
    Do 
         
        If IsEmpty(ActiveCell) = False Then 
            ActiveCell.Offset(1, 0).Select 
        End If 
         
    Loop Until IsEmpty(ActiveCell) = True 
     
     
     'in order to put the correct age group into column B
     
     
    If OptUnder18 = True Then 
        ActiveCell.Offset(0, 1).Value = "Under 18" 
         
    ElseIf Opt1864 = True Then 
        ActiveCell.Offset(0, 1).Value = "18-64" 
         
    ElseIf Opt6574 = True Then 
        ActiveCell.Offset(0, 1).Value = "65-74" 
         
    Else 
        ActiveCell.Offset(0, 1).Value = "75+" 
         
    End If 
     
     'selected values from the comboboxes get put into column C to I on the same row
     
    ActiveCell.Offset(0, 2).cboClientCategory.Value 
    ActiveCell.Offset(0, 3).cboReferralType.Value 
    ActiveCell.Offset(0, 4).cboReferralSource.Value 
    ActiveCell.Offset(0, 5).cboReferralReason.Value 
    ActiveCell.Offset(0, 6).cboReferralReason2.Value 
    ActiveCell.Offset(0, 7).cboReferralReason3.Value 
    ActiveCell.Offset(0, 8).txtReferralReason.Value 
     
     'puts the selected ethnicity into column J
     
    If OptWhiteBrit = True Then 
        ActiveCell.Offset(0, 9).Value = "White British" 
    ElseIf OptWhiteIrish = True Then 
        ActiveCell.Offset(0, 9).Value = "White Irish" 
    ElseIf OptWhiteOther = True Then 
        ActiveCell.Offset(0, 9).Value = "White Other" 
    ElseIf OptMixedCaribbean = True Then 
        ActiveCell.Offset(0, 9).Value = "Mixed Caribbean" 
    ElseIf OptMixedAfrican = True Then 
        ActiveCell.Offset(0, 9).Value = "Mixed African" 
    ElseIf OptMixedAsian = True Then 
        ActiveCell.Offset(0, 9).Value = "Mixed Asian" 
    ElseIf OptMixedOther = True Then 
        ActiveCell.Offset(0, 9).Value = "Mixed Other" 
    ElseIf OptAsianIndian = True Then 
        ActiveCell.Offset(0, 9).Value = "Asian Indian" 
    ElseIf OptAsianPakistani = True Then 
        ActiveCell.Offset(0, 9).Value = "Asian Pakistani" 
    ElseIf OptAsianOther = True Then 
        ActiveCell.Offset(0, 9).Value = "Asian Other" 
    ElseIf OptBlackCaribbean = True Then 
        ActiveCell.Offset(0, 9).Value = "Black Caribbean" 
    ElseIf OptBlackAfrican = True Then 
        ActiveCell.Offset(0, 9).Value = "Black African" 
    ElseIf OptBlackOther = True Then 
        ActiveCell.Offset(0, 9).Value = "Black Other" 
    ElseIf OptChinese = True Then 
        ActiveCell.Offset(0, 9).Value = "Chinese" 
    ElseIf OptChineseOther = True Then 
        ActiveCell.Offset(0, 9).Value = "Chinese Other" 
    ElseIf OptNotStated = True Then 
        ActiveCell.Offset(0, 9).Value = "Not Stated" 
         
    End If 
     
     'puts the FACS eligibility into column K of the sanme row
     
    If OptLow = True Then 
        ActiveCell.Offset(0, 10).Value = "Low" 
    ElseIf OptModerate = True Then 
        ActiveCell.Offset(0, 10).Value = "Moderate" 
    ElseIf OptSubstantial = True Then 
        ActiveCell.Offset(0, 10).Value = "Substantial" 
    ElseIf OptCritical = True Then 
        ActiveCell.Offset(0, 10).Value = "Critical" 
         
    End If 
     
     'selected values from the comboboxes get put into column L to N on the same row
     
    ActiveCell.Offset(0, 11) = cboOutcome1.Value 
    ActiveCell.Offset(0, 12) = cboOutcome2.Value 
    ActiveCell.Offset(0, 13) = cboOutcome3.Value 
     
    Range("B2").Select 
     
End Sub 
 
Private Sub CmdClear_Click() 
    Call UserForm_Initialize 
End Sub 
 
Private Sub CmdCancel_Click() 
    Unload Me 
End Sub 
 
Private Sub UserForm_Initialize() 
     
    txtReferralReason.Value = "" 
     
    With cboClientCategory 
        .AddItem "Carers [Aged Under 18]" 
        .AddItem "Carers [Aged 18-64]" 
        .AddItem "Carers [Aged 65-74]" 
        .AddItem "Carers [Aged 75+]" 
        .AddItem "Older People (Not EMI) [Aged 65-74]" 
        .AddItem "Older People (Not EMI) [Aged 75+]" 
        .AddItem "Physical Disability/Sensory Impairment [Aged 18-64]" 
        .AddItem "Learning Disability [Aged 18-64]" 
        .AddItem "Mental Health / EMI [Aged 18-64]" 
        .AddItem "Mental Health / EMI [Aged 65-74]" 
        .AddItem "Mental Health / EMI [Aged 75+]" 
        .AddItem "Substance Misuse [Aged 18-64]" 
        .AddItem "Other Vulnerable People [Aged 18-64]" 
         
    End With 
    cboClientCategory.Value = "" 
     
    With cboReferralType 
        .AddItem "New Client" 
        .AddItem "1st Contact  of year for an existing client" 
        .AddItem "Repeat (i.e. Subsequent contact within the year)" 
         
    End With 
    cboReferralType.Value = "" 
     
    With cboReferralSource 
        .AddItem "Primary/Community Health (GP's etc)" 
        .AddItem "Secondary Health (A&E, Hosptial, OT etc)" 
        .AddItem "Self Referral" 
        .AddItem "Family / Friend / Neighbour" 
        .AddItem "Sefton Social Services Dept" 
        .AddItem "LA Housing Dept or Housing Association" 
        .AddItem "Other Dept of Sefton LA or Other LA" 
        .AddItem "Legal Agency (Police, Court, Solicitor etc)" 
        .AddItem "Other" 
        .AddItem "Not Known" 
         
    End With 
    cboReferralSource.Value = "" 
     
    With cboReferralReason 
        .AddItem "Information & advice" 
        .AddItem "Accident prevention" 
        .AddItem "Security review" 
        .AddItem "Equipment & Adaptations" 
        .AddItem "Carer Support" 
        .AddItem "Other (please specify)" 
         
    End With 
    cboReferralReason.Value = "" 
     
    With cboReferralReason2 
        .AddItem "Information & advice" 
        .AddItem "Accident prevention" 
        .AddItem "Security review" 
        .AddItem "Equipment & Adaptations" 
        .AddItem "Carer Support" 
        .AddItem "Other (please specify)" 
         
    End With 
    cboReferralReason2.Value = "" 
     
    With cboReferralReason3 
        .AddItem "Information & advice" 
        .AddItem "Accident prevention" 
        .AddItem "Security review" 
        .AddItem "Equipment & Adaptations" 
        .AddItem "Carer Support" 
        .AddItem "Other (please specify)" 
         
    End With 
    cboReferralReason3.Value = "" 
     
    With cboOutcome1 
        .AddItem "Referral to other agency/organisation" 
        .AddItem "Provision of advice and information" 
        .AddItem "Promotion of independence" 
        .AddItem "Provision of equipment - Same Day" 
        .AddItem "Provision of equipment - Within 7 Days" 
        .AddItem "Provision of equipment - Within 3 Weeks" 
        .AddItem "Installation of adaptation" 
        .AddItem "Repairs undertaken" 
        .AddItem "Crime prevention" 
        .AddItem "Safety assessment" 
        .AddItem "Volunteers" 
         
    End With 
    cboOutcome1.Value = "" 
     
     
    With cboOutcome2 
        .AddItem "Referral to other agency/organisation" 
        .AddItem "Provision of advice and information" 
        .AddItem "Promotion of independence" 
        .AddItem "Provision of equipment - Same Day" 
        .AddItem "Provision of equipment - Within 7 Days" 
        .AddItem "Provision of equipment - Within 3 Weeks" 
        .AddItem "Installation of adaptation" 
        .AddItem "Repairs undertaken" 
        .AddItem "Crime prevention" 
        .AddItem "Safety assessment" 
        .AddItem "Volunteers" 
         
    End With 
    cboOutcome2.Value = "" 
     
     
    With cboOutcome3 
        .AddItem "Referral to other agency/organisation" 
        .AddItem "Provision of advice and information" 
        .AddItem "Promotion of independence" 
        .AddItem "Provision of equipment - Same Day" 
        .AddItem "Provision of equipment - Within 7 Days" 
        .AddItem "Provision of equipment - Within 3 Weeks" 
        .AddItem "Installation of adaptation" 
        .AddItem "Repairs undertaken" 
        .AddItem "Crime prevention" 
        .AddItem "Safety assessment" 
        .AddItem "Volunteers" 
         
    End With 
    cboOutcome3.Value = "" 
     
     
    OptUnder18 = False 
    Opt1864 = False 
    Opt6574 = False 
    Opt75 = False 
    OptWhiteBrit = False 
    OptWhiteIrish = False 
    OptWhiteOther = False 
    OptMixedCaribbean = False 
    OptMixedAfrican = False 
    OptMixedAsian = False 
    OptMixedOther = False 
    OptAsianIndian = False 
    OptAsianPakistani = False 
    OptAsianOther = False 
    OptBlackCaribbean = False 
    OptBlackAfrican = False 
    OptBlackOther = False 
    OptChinese = False 
    OptChineseOther = False 
    OptNotStated = False 
    OptSubstantial = False 
    OptModerate = False 
    OptCritical = False 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I then created a Word Art button on the worksheet that is supposed to bring up the form so that a user can begin inputing, the code i used to do this is on sheet2:


	VB:
	
 OpenForm() 
    VolOrgs.Show 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
When i click on the button assigned to the open form macro i get a run-time error 70 "Permission Error". And the debugger highlights the following line of text in yellow:

	VB:
	
VolOrgs.Show 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I have checked the File, Permissions menu and it is set to unrestricted access.

Does anyone have any suggestions or a possible solution for this?

Id be extremely grateful if somebody could help me resolve this issue.

Regards
Damian

I am using VBA to put a formula into a cell. The formula is


	VB:
	

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
But when I try to put this into a cell using the value option, I get a run time error 13: Type Mismatch. No problems while im compiling it but it comes up only during execution


	VB:
	
Sheets("Summation Table Template").range("B4").Value = "= IFERROR(VLOOKUP(LEFT(A5,11),'New Quarterly
Report'!A$11:E$37,3,FALSE), " - ")" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
When I try to put only the VLOOKUP, it works fine.


	VB:
	
Sheets("Summation Table Template").range("B4").Value = "= VLOOKUP(LEFT(A5,11),'New Quarterly
Report'!A$11:E$37,3,FALSE)" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

But I want the cell to not show #N/A when a value through vlookup is not found. Other formulas Iv tried are to use ISERROR with IF and also to store the formaula in a string and then put it into Range.Value but it still gives me a run time error 13. Why ?????

Ok easy one guys.

You run the following code on an empty worksheet (only a header, completely useless worksheet)


	VB:
	
Selection.Subtotal GroupBy:=11, Function:=xlSum, TotalList:=Array(13), _ 
Replace:=True, PageBreaks:=False, SummaryBelowData:=True 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Since there is nothing to sub total you get an error:

Run-time error '1004'
The command could not be completed by using the range specified. Select a single cell within the range and try the command again.

Now, fair enough. I did something that is impossible, add up nothing. But is there a way to make the script ignore the error, pretend like it never existed and just go along its merry way.

An example of VBA code would be very very appreciated.

Thanx

I've written some VBA code that takes a file, makes multiple copies of that file but formats each of those copies differently, and then emails the file through Outlook.

The code has worked well for at least 6 months. The code still works well on my machine. But the person who uses the code each day has run into a problem today.

Midway through the code, she gets this 3-line error message:

Run-time error '-2147417848 (800010108)':
Automation error
The object invoked has disconnected from its clients.

This brings the code to a halt and DR Watson pays a visit while the VB Editor and Excel are killed.

Any ideas on trouble shooting this? I've already checked drive space and the local and network drives each have several GB free space. Thanks for your help in advance!

Hello folks

First post by a one-week-in user of VBA. Thanks to those questioners and contributors whose posts have helped me get to this point.

I have been developing code to update data in two workbooks and then transfer rows of data within and between them. I'm using Excel 2003.

My code is probably pretty clumsy, but it works in the two workbooks I wrote it in. Knowing the workbooks would be shared in the final environment I checked the restrictions on shared workbooks and, as far as I know, I haven't broken these.

The interface is all through user-forms with the bulk of the code running on a click of OK.

When I change to the workbook to Shared and start using it (still without any other versions running) I get run-time error messages. This happens whether I'm calling a macro that moves rows of data or one that simply updates cells an existing record and also happens when only one of the worklbooks is open and called to do an activity that does not reference the other.

Undoing the share option gets everything working normally again.

The two run-time error messages I've seen most are 1004 "Unable to set the MergeCells property of the Range class" and 9 "Subscript out of range". The only merged cells are on the top row of each worksheet (where the macro buttons are) and shouldn't be affected by any of the activities I'm running.

Do I need to revisit my code (about 10 routines) section by section or are there "known issues" and workarounds for macros in shared workbooks.

I haven't posted any code, because this seems to be an issue regardless of the macro called (and because my code is about as elegant as bull in a frock).

Thanks for reading this far,

Alan

Hi,

I wonder if you can help me?
I get this error message with my VBA code

Run-time error '1004':
Unable to set the ColorIndex property of the Interior Class

When I debug, the red line below appears to be the problem. Can anyone help? Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("E7:E14")) Is Nothing Then
Select Case Target
Case "": icolor = 2
Case "Ongoing": icolor = 6
Case "Not Started": icolor = 15
Case "On Schedule": icolor = 45
Case "Behind": icolor = 3
Case "Completed": icolor = 4
Case Else
'Whatever
End Select

Target.Interior.ColorIndex = icolor

End If

End Sub

I'm getting a "Run-time error '1004" "application defined or object defined error"
with the following VBA script. The debug feature highlights this code:
Cells(upper, lower).Value = "fred"

What the heck? I don't get what the problem is!

VBA script:

Sub test()

Dim lower As Integer
Dim upper As Integer

lower = 1
upper = 10

While lower < upper
Cells(upper, lower).Value = "fred"
lower = lower + 1
Wend
End Sub

Hello,

I am a new member to this forum and I have an VBA related issue that I am hoping someone can help me figuring out the solution.

I have used the following VBA in excel 2007 and it workd fine. It is just that when I tried to use the same VBA in 2003 I get run time error 438. Here's the VBA and the line (bold) which is highlighted when the VBA is excuted in 2003.
Private Sub OptionButton1_Click()
Application.ScreenUpdating = False
Range("B4:I10").Select
    ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add Key:=Range("C4:C10") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet3").Sort
        .SetRange Range("B4:I10")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("B3").Select
Application.ScreenUpdating = True
End Sub

I appreciate if some one can let me know what's wrong here and how to fix it, keeping in mind that the above VBA woks fine in 2007 but gives the run time error 438.

Regards,

Hello,

I was wondering if someone may be able to help me with a Macro I am attempting to get to work.

It's quite a long, complicated (for me anyway) thing, but I am receiving a Run-time Error '1004' Method 'Union' of object '_Global' failed.

The area of the VBA it is referring to is: Set BoldRange = Union(BoldRange, c)
the aim of which is to find Bold cells and remove the conditional formatting from them.

Does anyone have any idea what this error message is objecting to?

Thanks,
Phil

I am running the code below and receiving Run-time Error 6: Overflow. I've noticed this error occurs when the spreadsheet being worked only has one row of data.

Do I need to "slow down" the code to solve this?

z = 2
For i = 1 To Range(Range("B2"), Range("B2").End(xlDown)).Count

If Range("B2").Offset(i, 0) = 1 Then
Range("B2").Offset(i, 0).Select
SrchText = Range("B2").Offset(i, 1).Value
Range("B2").Offset(i, 1).Select
For j = 1 To Range(Range("C1"), Range("C1").End(xlDown)).Count
Range("C1").Offset(j, 0).Select
Range("C1").Offset(j, -1).Select
Range("C1").Offset(j, 14).Select
Range("B2").Offset(i, 1).Select

If Range("C1").Offset(j, 0) = SrchText And Range("C1").Offset(j, -1) = 0 Then
Range("C1").Offset(j, 0).Interior.Color = vbYellow
lettercode = Range("C1").Offset(j, 13).Value
Range("C1").Offset(j, 0).Select

End If
Next j
End If
Next i

Thanks for taking a look at this,
John

Hi,

I have created a macro which sorts 4 columns of data in asceding order numerically. I have assigned this macro to a button so that when the button is clicked, the columns of data are sorted.

Unfortunately when the button is pressed i get a vba run-time error 1004. I havent a clue how to resolve this. I recorded the macro useing the excel recorder function as opposed to typing it out manually.

The code created is given below. If i click debug when the error pops up, the code referring to column f:f is highlighted yellow.

Can anyone help in stopping this error occuring.

Thanks


	VB:
	
 MacroAscending() 
     '
     ' MacroAscending Macro
     ' Macro recorded 25/10/2006
     '
     
     '
    Columns("F:F").Select 
    Range("A1:F185").Sort Key1:=Range("F1"), Order1:=xlAscending, Header:= _ 
    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
    DataOption1:=xlSortNormal 
    Columns("M:M").Select 
    Range("H1:M185").Sort Key1:=Range("M1"), Order1:=xlAscending, Header:= _ 
    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
    DataOption1:=xlSortNormal 
    Columns("T:T").Select 
    Range("O1:T185").Sort Key1:=Range("T1"), Order1:=xlAscending, Header:= _ 
    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
    DataOption1:=xlSortNormal 
    Columns("AA:AA").Select 
    Range("V1:AA185").Sort Key1:=Range("AA1"), Order1:=xlAscending, Header:= _ 
    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
    DataOption1:=xlSortNormal 
    Range("A1").Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi,

I'm guessing this is not (easily) possible, but I thought I might just ask in case: Can we change the messages in VBA run-time errors displayed by Excel. Better yet, I would like to avoid the Excel generated error box, and just have a text message written into a particular cell.

Thanks
Helen

Hi people. Just want to say thanks to everyone who has helped me with my previous posts, the information you have given me has been invaluable.

My aim was to move towards autoreporting and I am now in my final stages. However my final hurdle involves a run time error 13.
I am using excel to pull data from access tables. However one table is connected to an OCDB oracle database. I've attached the code below, The connection seems to be working as it asks me to enter the password but then I get the error 13 "type mismatch" and it highlights


	VB:
	
 rs = db.OpenRecordset("SELECT * FROM " & TableName & _ 
" WHERE " & FieldName & _ 
" = " & Chr$(34) & MyCriteria & Chr$(34), dbReadOnly) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
"

Here is the full code below


	VB:
	
 Test1() 
    Dim DBFullName As String 
    Dim TableName As String 
    Dim FieldName As String 
    Dim TargetRange As Range 
    Dim MyCriteria As String 
    Dim cnn As ADODB.Connection 
    Dim rs As ADODB.Recordset 
    Set cnn = New ADODB.Connection 
    Set rs = New ADODB.Recordset 
    Dim db As database 
    Dim intColIndex As Integer 
     
    DBFullName = "D:Documents and SettingsE400845DesktopCopy of ChemToast2007 QRY.accdb" 
    TableName = "SAMPLE" 
    FieldName = "SAMPLEID" 
    MyCriteria = Sheets("Sheet1").Range("A2").Value 
    cnn.Open "SAPPHIRE 10", "SAPPHIRE", "SAPPHIRE" 
    rs.ActiveConnection = cnn 
    rs.CursorLocation = adUseServer 
     
    MyCriteria = Sheets("Sheet1").Range("A2").Value 
    Set TargetRange = Range("A6") 
    Set db = OpenDatabase(DBFullName) 
    Set rs = db.OpenRecordset("SELECT * FROM " & TableName & _ 
    " WHERE " & FieldName & _ 
    " = " & Chr$(34) & MyCriteria & Chr$(34), dbReadOnly) 
     
    rs.Open 
    rst.Close 
    Set TargetRange = TargetRange.Cells(1, 1) 
    Set db = OpenDatabase(DBFullName) 
     
     
    For intColIndex = 0 To rs.Fields.Count - 1 
        TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name 
         
    Next 
     ' write recordset
    TargetRange.Offset(1, 0).CopyFromRecordset rs 
     
    Set rst = Nothing 
    Set cnn = Nothing 
    Set rs = Nothing 
    db.Close 
    Set db = Nothing 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Any help would be great, I think it might just be something silly!

HI everyone!!!

I have a small problem running Vb

I have a userform that im trying to load up and Im getting an error message.

Run-time error '1004'
Method 'Range' of object_'Global' failed.

Whats causing this to happen??

Thanks in advance.

Shane

Hi board,

I always get an error (run time error 1004) when I try to insert some columns and I don't know why. Can someone help me please? I marked in the code where the error is coming

Code:
Range("A1:D1").EntireColumn.Insert
Range("A1") = "Year"
Range("B1") = "Month"
Range("C1") = "Plant"
Range("D1") = "ItemClass"

Range("G1:H1").EntireColumn.Cut
Range("E1").Insert
Range("G1:H1").EntireColumn.Insert
Range("G1") = "Key_4"
Range("H1") = "Key_5"
Range("K1:L1").EntireColumn.Insert
Range("K1") = "ItemClass"
Range("L1") = "PlanningClass"
Range("S1").EntireColumn.Cut
Range("M1").Insert

totalrows = Range("E1").CurrentRegion.Rows.Count

For i = 2 To totalrows
    Cells(i, 1) = year
    Cells(i, 2) = month
    plant = Cells(i, 17)
        Select Case plant
        Case "Zeebrugge"
            plant = "S25"
        Case "Hermes"
            plant = "S90"
        Case "Borgloon"
            plant = "S20"
        Case "Copella"
            plant = "S35"
        Case Else
            plant = "S25-OTHER"
        End Select
    Cells(i, 3) = plant
    Cells(i, 4) = Cells(i, 3) & "%3"
    Cells(i, 11) = 3
    Cells(i, 12) = Cells(i, 5)
    Cells(i, 26) = Cells(i, 3) & "%" & Cells(i, 5)
    Cells(i, 27) = Cells(i, 3) & "%" & Cells(i, 6)
    
Next i

Range("N1:U1").EntireColumn.Delete

Range(Cells(2, 18), Cells(totalrows, 19)).Copy
Range("E2").Select
ActiveSheet.Paste

Range("R1:S1").EntireColumn.Delete
Range("N1:01").EntireColumn.Insert '--> HERE I'M GETTING THE ERROR
Range("Q1:AC1").EntireColumn.Insert
Range("AE1").EntireColumn.Cut
Range("Z1").Insert
Range("AE1").EntireColumn.Cut
Range("AD1").Insert
thanks a lot for your help!

Hello All:

I'm stuck here, below is the part of my code that is giving me troubles:

Windows("DRAWBACK-BUSHYPARK-JAN08-DEC08.xls").Activate
Sheets("BUSHY-PARK-HEALTHCARE").Select
Range("A50000").Select
ActiveSheet.Paste

I'm getting a Run-time error '9': Subscript out of range on the Sheets line 2. I've checked the spelling. Everything is in order. There are three sheets to chose from. Can anyone guess why this may be happening.

I am running a module in Access that exports data to Excel. When I ran this
DB on Windows 2000, I had no errors.

After switching to Windows XP, I get an error that debugs to the VBA code in
Access.

Once the macros is running, the Excel spread sheet opens. If I click on
another tab in the worksheet I get this error in my Acess VBA:

Run-time error '50290':
Application-defined or object-defined error

I am not sure where this problem stems from: Access, Excel, VBA or Windows
XP.

Any advice would be greatly appreciated!

Thanks in Advance,
Elena

Hi,

Lucky for me, I have been tasked with 'fixing' a button within a rather large and complex spreadsheet. The button merely refreshes a macro, which pulls data from a specific column in a separate worksheet - I'll call this Column A. The refresh works fine as long as a cell in Column A is selected when I click the button. However, if a cell outside of Column A is selected, and I try to refresh, I receive a VBA run-time error 1004 with the narrative 'Application-defined or object-defined error' (which I presume is indicating a cell within Column A needs to be selected) with the options to End or Debug. As a lot of people use this workbook, keeping a cell in Column A permenantly selected proves a little tricky.

My question is: can I alter the existing macro/create a new macro so that it doesn't matter whether a cell in Column A is selected?

All suggestions welcome.

Please Help,

I have run across a problem where I am forced to use a VBA macro in bringing up a word document (the word document is setup with a Password). When clicking on the macro, a pop up window will appear asking for a Password! I can view the word document (Read Only, which works great ), but if click on “CANCEL” or the OK Button, A Microsoft Visual Basic Error Debug Pop Up Window will appear giving a error code of “Run Time Error 5408” :o . ( ***Please remember to have the word document setup with a Password.***)

Below is what I have. Is there a way to trap the error code?

Sub PRD77()

Dim appWord As New Word.Application
Dim docWord As Word.Document

Set docWord = appWord.Documents.Open("S:VB2 Product DevelopmentPRDsPRD#77-

Instant VB2 User RegistrationPRD#77-InstantVB2UserRegistration.doc")

appWord.Visible = True

End Sub

Please help...