Posts by NoSparks

Perhaps this Send an email through Gmail using VBA site can/will shed more light on achieving what you're wanting.

OK, I had the file name screwed up when originally using DIR

this should work for you

Sub LoopThroughFiles()

Dim chk As CheckBox

folderName = "C:\Users\BSCAMPO1\Desktop\Orçamentos Geral"
If Right(folderName, 1) <> Application.PathSeparator Then folderName = folderName & Application.PathSeparator

Fname = Dir(folderName & "*.xlsx")

'loop through the files
Do While Len(Fname)
With Workbooks.Open(folderName & Fname)
With .Sheets("Orçamento")
Set chk = .CheckBoxes.Add(Left:=.Range("L12").Left, Top:=.Range("L12").Top, Width:=.Range("Y12").Width, Height:=.Range("L12").Height)
End With
With chk
.Caption = ""
.LinkedCell = "Parâmetros!A" & 27
End With
.Close savechanges:=True
End With
Fname = Dir
Loop
End Sub

I could get your checkboxes installed in the workbooks but couldn't stop the loop when using Dir and ended up with multiple checkboxes on top of each other in the cells.

Adapted the late binding macro from this site and came up with this which seems to work.

Sub LoopAllFilesInFolder()

Dim folderName As String
Dim FSOLibrary As Object
Dim FSOFolder As Object
Dim FSOFile As Object
Dim chk As CheckBox

'Set the folder name to a variable
folderName = "D:\OZtesting\"        '<~~~~~~~~~ CHANGE TO YOUR SPECIFIC FOLDER

'Set all the references to the FSO Library
Set FSOLibrary = CreateObject("Scripting.FileSystemObject")
Set FSOFolder = FSOLibrary.GetFolder(folderName)
Set FSOFile = FSOFolder.Files

'Use For Each loop to loop through each file in the folder
For Each FSOFile In FSOFile
If Right(FSOFile.Name, 4) = "xlsx" Then
With Workbooks.Open(FSOFile)
With .Sheets("Orçamento")
Set chk = .CheckBoxes.Add(Left:=.Range("L12").Left, Top:=.Range("L12").Top, Width:=.Range("Y12").Width, Height:=.Range("L12").Height)
End With
With chk
.Caption = ""
.LinkedCell = "Parâmetros!A" & 27
End With
.Close savechanges:=True
End With
End If
Next

'Release the memory
Set FSOLibrary = Nothing
Set FSOFolder = Nothing
Set FSOFile = Nothing

End Sub

Quote

2. Excel places the date into the cell as Aug-1

will Left(the cell.Text, 3) get what you're after ?

Adding to what jolivanes has indicated:

"*" will find first non blank cell

"" will find first blank cell

But range.Find is a circular function that begins looking after the first cell in the stated range and circles back around to first cell, it being the last cell checked.

So if the first cell in the range may be the first blank, but not the only blank, you'll need to specify the after cell.

try

Set u6 = wksht1.Range("AA11:AA19").Find(What:="", After:=wksht1.Range("AA19"), SearchOrder:=xlRows, SearchDirection:=xlNext, LookIn:=xlValues)

Interesting

How did you figure that out in post #4 without realizing that you are counting blank rows and only doing something when 2 are accumulated ?

Have a look at the attached file

this question is a cross post of
https://www.mrexcel.com/forum/excel-...umn-added.html

In the description of the After parameter at the link provided. It says

[SIZE=14px]The cell after which you want the search to begin...
Notice that After must be a single cell in the range.
Remember that the search begins after this cell;
the specified cell isn't searched until the method wraps back around to this cell.
If you do no specify this argument, the search starts after the cell in the upper-left corner of the range.[/SIZE]

Something in your parameter settings, likely LookAt, which is a persistent parameter, was last used to find xlPart and is now determining 11/1/YY is a match so the .Find never wraps back around to the first cell.

Quote

On another workbook I have basically the same setup but starting at A2 with the same issue. It's almost like it doesn't actually see the 1/1/YY

It doesn't sound like you tried changing the start of Set DateHeader from A3 to A2.
Did you read the description of the After parameter at the link provided ?

You didn't read what I wrote...

within the formula in the macro double quotes will need to be doubled up

Range("G2").Formula = "=IF(AND(WEEKDAY(C3)< 7, WEEKDAY(C3)> 1),""CStd"",IF(WEEKDAY(C3)=7,""CSat"",""CSun""))"

Can you manually put that exact formula into G2 and have Excel accept it ?

If not, correct it then within the formula in the macro double quotes will need to be doubled up.

see if this alteration helps

''I have split it into 2 for trouble shooting purposes
    'MsgBox Hour(Sheets("Build Data").Range("O" & PasteRow).Value) & "hours and " & Minute(Sheets("Build Data").Range("O" & PasteRow).Value) & " minutes"
    MsgBox Int((Sheets("Build Data").Range("O" & PasteRow).Value) * 24) & " hours and " & Minute(Sheets("Build Data").Range("O" & PasteRow).Value) & " minutes"
    'MsgBox Hour(Sheets("Build Data").Range("N" & PasteRow).Value) & "hours and " & Minute(Sheets("Build Data").Range("N" & PasteRow).Value) & " minutes"
    MsgBox Int((Sheets("Build Data").Range("N" & PasteRow).Value) * 24) & " hours and " & Minute(Sheets("Build Data").Range("N" & PasteRow).Value) & " minutes"

Attaching the spreadsheet instead of a picture of it would be better.

Have you tried using the full set of parameters of the Range.Find Method ?
Note that some parameters are persistent and don't revert to a default value so unless you specify them you may not be using .Find the way you think.

What happens if you change the start of the DateHeader range to A1 or A2 ?

This line of Select_All()

ActiveSheet.Range(Selection, Selection.End(xlDown)).Select

results in 1048571 rows being selected for copy, and there aren't that many rows available to paste to.
I tried this and it works. I wouldn't think the 5 blank rows being pasted at the bottom would matter.

Sub Select_ALL()
'
' Select_ALL Macro
'
' Keyboard Shortcut: Ctrl+Shift+A
'


    Range("A6").CurrentRegion.Offset(5).Select


End Sub

Do you not have the second jump as rTarget.Offset(, 1) when it should be 7 ?

Re: Excel VBA code using MsgBox Join(Application.WorksheetFunction.Transpose(Range("n

Your command buttons currently call the Vendor and Chart subs.
Determine the range(s) where a change should cause an update and use the Worksheet_Change event to call the sub.
(You'll want to disable events while the subs run)

Re: Excel VBA code using MsgBox Join(Application.WorksheetFunction.Transpose(Range("n

Simplest way to 'scroll & continue to display all the items to the end' would be to create a user form (the shape of a message box) with a single listbox filling the entire form and show it rather than the message box. Populate the list box at initialization.

Private Sub UserForm_Initialize()
Me.ListBox1.List = Range("newvendor").Value
End Sub