VBA IF Cell is NOT Empty input this Text

[ItalianPlatinum]

Having trouble coming up with VBA that will kick me out exactly what I need. I am looking for something for example:

If cell B2 is not empty then type this in A2, this in C2. and repeats for B3, B4 until B... is empty.

 

[Zack Barresse]

The way the code is in your last post, you'd basically have to make two calls and they would have to match in locations. This is not ideal programming. We should call it once and have it run the action based on a single [column] input. This reduces the potential for error and narrows the view for debugging/maintenance. In other words, better practices. I'd recommend ditching the above and going with the latest code i sent. The bottom 3 routines are just helper and you should only focus on the top routine. It may look like a lot but it's not really. It's more fleshed out because of it being a bit more robust and I handled some errors in it.

 

[ItalianPlatinum]

Only issue is im getting a syntax error on this one. just want it to tell me current day

UpdateColumnValues FoundRange, "T", "Format(Now, "MM/DD/YYYY")", LastRow, 2, Sheet, True

 

[Zack Barresse]

Take the quotes off the Format function.

UpdateColumnValues FoundRange, "T", Format(Now, "MM/DD/YYYY"), LastRow, 2, Sheet, True

 

[ItalianPlatinum]

Thanks Zack. Appreciate the help on this.

 

[ItalianPlatinum]

Any idea how it is putting 8/31/1987 when doing that?

UpdateColumnValues FoundRange, "T", Format(Now, "MMDDYY"), LastRow, 2, Sheet, True

 

[Zack Barresse]

You have a format applied to that cell range (column T). The value passed by

Format(Now, "MMDDYY")

is "032020", which is a string, but when applied to the grid Excel see's it as numeric and applies that. The cells also have a short date format applied, so you see the date of August 31, 1987, or 8/31/1987. So the result is expected and correct. If you want the cell to show "32020" (without quotation marks) then change the 'ClearContents' portion of the code to 'Clear', which will clear all values and formats.

 

[ItalianPlatinum]

Yup that's it I had it as date but was inputting a value. So I changed it to MM/DD/YYYY. I kept the clear contents.

I have another small code that will open a dialogbox and set the file as csv is there a way to pre-populate the final name? when it does.

Dim wb As Boolean
ChDir "MY PATH"
wb = Application.Dialogs(xlDialogSaveAs).Show(Arg2:=xlCSV)

 

[Zack Barresse]

You can with Application.GetSaveAsFileName(). Here's an example of prompting for a filename and saving a copy. This assumes the workbook structure is valid for CSV file format.

Sub GetNameAndSaveAsCSV()

    Dim TargetBook As Workbook
    Dim TargetPath As String
    Dim TargetName As String
    Dim SaveName As Variant
    
    TargetPath = "C:\"
    TargetName = "Initial filename.csv"
    
    SaveName = Application.GetSaveAsFilename(InitialFileName:=TargetPath & TargetName, FileFilter:="CSV File (*.csv),*.csv")
    If SaveName = False Then
        ' User cancelled
    Else
        
        If ExistingFile(SaveName) Then
            ' File already exists...
        Else
            
            ThisWorkbook.SaveCopyAs SaveName
            
        End If
        
    End If
    
End Sub

 

[ItalianPlatinum]

This part is giving me a compile error:

If ExistingFile(SaveName) Then
' File already exists...

 

[Zack Barresse]

Ah, right, it's a separate function call. Here is the function:

Public Function ExistingFile( _
       ByVal FilePath As String _
       ) As Boolean
    Dim Attributes As Integer
    On Error Resume Next
    Attributes = GetAttr(FilePath)
    ExistingFile = (Err.Number = 0) And (Attributes And vbDirectory) = 0
    Err.Clear
End Function