1

I am very new to VBA. runtime error 424 starts when I try to set value1 =. Can't quite seem to figure this one out. I am dealing with imported data. Negative numbers are imported with the CR suffix going to an adjacent column. I need excel to recognize the CR and make the cell next to it negative or what I have tried now is to return the negative value in a third column. The data skips rows so I only need the true negative numbers to be translated everything else should return a blank.

I have tried conditional formatting using an if equation which worked well but when translated to VBA the relative cell references and formulas were messy and not user freindily in case of format changes,

included the problem parts of the code.

Thanks in advance to anyone who can help!

    Application.ScreenUpdating = False

Sheet2.Range("A1:H9999").ClearContents

Dim vFileName
Dim MyWorkbook As Workbook
Dim NameColumn As Range
Dim value1 As Range
Dim value2 As Range
Dim value3 As Integer


vFileName = Application.GetOpenFilename("Text Files (*.txt),*.txt")



MyWorkbook.Activate
Application.DisplayAlerts = False
ActiveWindow.Close
Set value1 = Worksheet.Range("K113:K249")
Set value2 = Worksheet.Range("H113:H249")
Set NameColumn = Worksheet.Range("I113:I249")
If NameColumn.Value = "CR" Then
    value1.Value = value1.Value * -1
    ElseIf value2.Value = 0 Or "" Then
    value1.Value = ""
    Else: value1.Value = value1.Value
End If
Explorer
  • 1,491
  • 4
  • 26
  • 67
bobby1683
  • 11
  • 2
  • 2
    Please comment out `Application.ScreenUpdating = False`. It's a great thing to add to your _working_ code - it makes life difficult when trying to debug non-functioning code. – FreeMan Jun 11 '15 at 18:43
  • 1
    `ElseIf value2.Value = 0 Or "" Then` doesn't look right; VBA would expect something like `ElseIf value2.Value = 0 Or value2.Value = "" Then`... don't know if that's related or not, though – LittleBobbyTables - Au Revoir Jun 11 '15 at 18:45

2 Answers2

3

In addition to the problem with ActiveWindow.Close, I don't see where you have given any value to Worksheet.

I would remove ActiveWindow.Close (what is that supposed to do anyway?) and replace the occurrences of Worksheet.Range by ActiveSheet.Range

John Coleman
  • 51,337
  • 7
  • 54
  • 119
1

You've closed the window with

ActiveWindow.Close

Then you're trying to set a range variable to something in the now closed workbook.

Remove that line and/or move it to the end of your code.

As LittleBobbyTables pointed out:

ElseIf value2.Value = 0 Or "" Then

isn't valid. Use this:

ElseIf value2.Value = 0 Or value2.Value = "" Then

Also

Else: value1.Value = value1.Value

Get rid of the colon after Else

Else value1.Value = value1.Value
Community
  • 1
  • 1
FreeMan
  • 5,660
  • 1
  • 27
  • 53
  • the activewindow.close is closing a window i had open earlier. – bobby1683 Jun 11 '15 at 18:51
  • 1
    Are you sure the other window is active, and not this one? With `Application.ScreenUpdating = False` in there, you might not be where you think you are. [How to avoid using Select in Excel VBA macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) to avoid this kind of confusion. Also, I think [John](http://stackoverflow.com/a/30789067/2344413) nailed another issue. – FreeMan Jun 11 '15 at 18:54