Posts by Macropheliac

Re: Closing Txt File Thru' Vba Without Save Changes Dialog Box Appearing

Maybe try turning Events off before and on after:

Application.DisplayAlerts=False
'Your Code'
Application.DisplayAlerts=True

or simply

.Close True
or 
.Close False

Re: Viewing Attachments In Current Threads

Try this attached example. I threw it together rather quickly, so it's not pretty but I think it works. Post back for assistance.

EDIT: In my haste, I forgot to mention in order for the code to work, you must set up a workbook named "Results Book.xls" with a worksheet with the name and headings mentioned.

Mac

Re: Viewing Attachments In Current Threads

Hello tpampel!

You say the results should be in a "totally different workbook". Would this workbook be a new workbook or an existing workbook? Where (Sheet name) in the workbook would the results be placed? Would the headings be the same as the headings in the current workbook?

With a little information, this should be a fairly simple task.

Mac

Re: Take File Name From Macro

Hello, zotee98!

Perhaps something like:

Range("A1").Value = Application.GetOpenFilename(MultiSelect:=True)

Mac

Re: Viewing Attachments In Current Threads

Hello again, tpampel!

I have plenty of time, now. Instead of rehashing a years old thread, why don't you explain your data layout and what you would like to accomplish. Perhaps you can even attach an example. I'm sure we can work this out.

Mac

Re: Preventing Calculation Error Showing

Hello, kjBox!

Try incorporating the IsError function:

IF(ISERROR([I]Your Formula Here[/I]),"[I]Result if error here[/I]",[I]Result if not error Here[/I])

Re: Copy Content Of The Cell Before Date

Try diming lngcmp as a string instead of long. This is probably where the problem lays.

Mac

Re: Viewing Attachments In Current Threads

In the following code:

Sub FindPaste()
Dim iCell As Range


For Each iCell In ActiveSheet.Range("A:A")
        If iCell.Value = "California" Then
                iCell.EntireRow.Copy Sheets("PasteSheet").Range("A65536").End(xlUp).Offset(1)
        End If
Next iCell
End Sub

iCell is a variable that represents a Range. This is used in a For Each loop to test each cell in the column. When the value "California" is found, the entire row is copied and pasted to the first available row on the "PasteSheet".

I will have time later, from home, to explain more. For now, do a search for variables, ranges, loops, and copy method. I'm sorry I do not have time, now.

I hope this helps,

Mac

Re: Copy Content Of The Cell Before Date

On which line do you receive the error?

Did you try changing:

lngCmp = InputBox("Please enter the date", "Begining of the week")

to:

lngCmp = DateValue(InputBox("Please enter the date", "Begining of the week") )

Mac

Re: If Cell Blank, Copy Prior Column Cell Value

Hello, jlsprink!

Perhaps something like this?

Sub FindFillBlanks()
Dim iCell As Range


For Each iCell In Range("D:D")
        If iCell.Value = "" Then
                iCell.Value = iCell.Offset(0, -1).Value
        End If
Next iCell
End Sub

Mac

Re: Viewing Attachments In Current Threads

In the attachment, Colomn A on sheet1 is searched for "California". The appropriate rows are copied to the "Paste Sheet".

Hope this helps,

Mac

Re: Copy Content Of The Cell Before Date

This worked in my test:

If DateValue(InputBox("Please enter the date", "Begining of the week")) < DateValue(Cells(i, 29).Value) Then
    Sheets("Result").Cells(6 + lngResultspast, 23) = Sheets("Tracker2").Cells(i, 5)
    Sheets("Result").Cells(6 + lngResultspast, 24) = Sheets("Tracker2").Cells(i, 8)
    Sheets("Result").Cells(6 + lngResultspast, 25) = Sheets("Tracker2").Cells(i, 10)
End If

Mac

Re: Viewing Attachments In Current Threads

I didn't realize you were looking for code. The message box was only to make you aware that the attachment was opened. What exactly are you seeking help with?

Mac

Re: Undo The Column Hide & Stop On Delete Sheet

Hello, jigar987!

How about protecting the workbook?

Mac

Re: Copy Content Of The Cell Before Date

Untested, but try changing this:

lngStart < Cells(i, 29) Then

to this:

DateValue(lngStart) < DateValue(Cells(i, 29) )Then

Mac

Re: Viewing Attachments In Current Threads

Hello, tpampel!

There doesn't appear to be an attachment in that particular thread. The thread is old and the attachment was probably removed. Try clicking the attachment below.

Mac

Re: Paste Column But Change Order

Hello, charlesa920!

If I understand correctly, the following will do it.

Sub TransferData()
Sheets("MANUAL ADJUSTMENTS").Range("A1:G" & Sheets("MANUAL ADJUSTMENTS").Range("G65536").End(xlUp).Row).Copy Sheets("TRANSACTIONS").Range("A1")
Sheets("MANUAL ADJUSTMENTS").Range("J1:K" & Sheets("MANUAL ADJUSTMENTS").Range("K65536").End(xlUp).Row).Copy Sheets("TRANSACTIONS").Range("J1")
End Sub

Mac

Re: Remove .xls From Header

Hello Foxxy1!

I'm not sure what you mean but this will give you the name of the current workbook without the .xls. Maybe you can adapt it.

Sub LoseThexls()
Dim i As Long


i = Len(ThisWorkbook.Name)
MsgBox Left(ThisWorkbook.Name, i - 4)
End Sub

Mac

Re: Taking Focus From Input Box Or Close On Workbook Deactivate

Hello bancey!

I'm guessing here, and you may correct me if I'm wrong, but I assume there is code in the Workbook_Open event of the second workbook. If this is the case, then the execution of code is already in process and the event code will not execute until the completion of the original code.

Perhaps if you could explain what it is you're attempting, we can come up with a work around.

Mac

Re: Matching Value

Maybe something like this:

Sub FindMatch()
Dim iCell As Range
Dim iValue As String
Dim iFind As Range


Dim TestRange As Range
Set TestRange = Sheets("Sheet 1").Range("A2:A" & Sheets("Sheet 1").Range("A65536").End(xlUp).Row)


Dim SearchRange As Range
Set SearchRange = Sheets("Sheet 2").Range("A2:A" & Sheets("Sheet 2").Range("A65536").End(xlUp).Row)


For Each iCell In TestRange
        iValue = iCell.Value
        With SearchRange
                Set iFind = .Find(iValue)
                If Not iFind Is Nothing Then
                        iCell.Offset(0, 1).Value = "True"
                Else
                        iCell.Offset(0, 1).Value = "False"
                End If
        End With
Next iCell


End Sub

The above assumes the two worksheet names are Sheet 1 and Sheet 2 and the columns in question are in Column A. The results are placed in column B of Sheet 1.

I hope it helps.
Mac