Posts by alfadaz

This works great..

Thank you!

I need to perform a count when the text in the cell starts with a set letter (in this case "S"), but the 2nd charector must be a number, not a letter.

Any help would be much appreciated.

TIA

Rory, you are a life saver...

Thanks for pointing this out, i've gone back to it a few times over the past few hours and didn't spot that!

Thanks again...

Hey all,

I'm sure this is an easy one for some, but i've been stuck for a while on this one...

I have a combo box, which i select an item from a list. The item selected is then set to a variable (string).

When just doing a single match, it works fine, but if i add the OR command, i get an type mismatch error? (line 32)

Using the watch window, everything is listed as a string. If i don't declare the item selected in the combobox, it appears in the watch window as Variant/String.

If profile = "PURLIN-TIE" Then

    Selection.Resize(Selection.Columns.Count + lastrow - 1, Selection.Rows.Count + 6).Select
    Selection.Copy
                
    Sheets("Schedule Template EXT").Select
    Range("B9").PasteSpecial Paste:=xlPasteValues
    
    
    Range("I9").Formula = "=IF([@[ASSEMBLY MARK]]="""","""",IF([@DESCRIPTION]=""PURLIN-TIE"",[@LENGTH]/1000*profiles!$M$9,IF([@DESCRIPTION]=""DIAGONAL TIE WIRE"",[@LENGTH]/1000*profiles!$M$10+profiles!$N$10)))"
    Range("J9").Formula = "=IF([@[PRICE EACH]]="""","""",[@[PRICE EACH]]*[@QUANTITY])"
    
    lastrow = Sheets("Schedule Template EXT").Cells(sht.Rows.Count, "B").End(xlUp).Row - 1
    Range("A9:L9").Copy
    Range("A10:L" & lastrow).PasteSpecial xlFormats
    
ElseIf profile = "DIAGONAL TIE WIRE" Then

    Selection.Resize(Selection.Columns.Count + lastrow - 1, Selection.Rows.Count + 6).Select
    Selection.Copy
                
    Sheets("Schedule Template EXT").Select
    Range("B9").PasteSpecial Paste:=xlPasteValues
        
    Range("I9").Formula = "=IF([@[ASSEMBLY MARK]]="""","""",IF([@DESCRIPTION]=""PURLIN-TIE"",[@LENGTH]/1000*profiles!$M$9,IF([@DESCRIPTION]=""DIAGONAL TIE WIRE"",[@LENGTH]/1000*profiles!$M$10+profiles!$N$10)))"
    Range("J9").Formula = "=IF([@[PRICE EACH]]="""","""",[@[PRICE EACH]]*[@QUANTITY])"
    
    lastrow = Sheets("Schedule Template EXT").Cells(sht.Rows.Count, "B").End(xlUp).Row - 1
    Range("A9:L9").Copy
    Range("A10:L" & lastrow).PasteSpecial xlFormats
    
ElseIf profile = "203 APEX TIE" Or "233 APEX TIE" Then

    Selection.Resize(Selection.Columns.Count + lastrow - 1, Selection.Rows.Count + 6).Select
    Selection.Copy
                
    Sheets("Schedule Template EXT").Select
    Range("B9").PasteSpecial Paste:=xlPasteValues
        
    lastrow = Sheets("Schedule Template EXT").Cells(sht.Rows.Count, "B").End(xlUp).Row - 1
    Range("A9:L9").Copy
    Range("A10:L" & lastrow).PasteSpecial xlFormats
    
    
Else
    MsgBox "EVERYTHING ELSE"
End If

Any help would be much appreciated...

Thanks

Darren

Thank you so much Fluff - that was driving me nuts

I'll add a line in my code to ensure all rows have a fixed height, as well as not hidden

Bit of a strange one this...

I have a spreadsheet that i import a load of data into sheet 1, this is usually between 750-1250 lines.

I do various things with the data and have some crosschecking to ensure all data was captured.

I have a =sum(d:d) in a cell to total up the column, but if i click the column and check the sum of the selected cells in the status bar at the bottom, the answers are different

I have checked to see if any of the numbers are stored as text. I couldn't find any...

I have changed the formatting from General to Number, no difference...

I have checked for hidden rows, i can't find any...

I am now a but stuck on what to check next

Any help would be appreciated..

Thanks

Daz

TEST MCS_copy2.xlsm

Not sure if it was the correct way of fixing, but i just used the ChDir command at the beginning of the code..

ChDir Application.ActiveWorkbook.Path & "\"

I am working on a project for work, where i need to create a batch file, run the file, then delete it.

The batch file looks for text files with a set name in the current folder and subfolders and combines them into one text file.

The batch file is running correctly, but it is running from 1 directory up.

For example, the excel file with the VBA code is here:

G:\Dropbox\DT\WORK\Phases - 101-102-103-104-105-106-107-108-109

But the batch file is being created here:

G:\Dropbox\DT\WORK\

Public Sub MCS_to_Accessories()

'STAGE 1
'Creates a batch file to combine all the AccReport.txt files together to create one Master text file, then deletes itself.

Const MY_FILENAME = "AccReportMerge.BAT"
Dim FileNumber As Integer
Dim retVal As Variant
FileNumber = FreeFile
Open MY_FILENAME For Output As #FileNumber
Print #FileNumber, "> ""AccReportMaster.txt"" rem/"
Print #FileNumber, "for /R %%I in (""AccReport.txt"") do copy ""AccReportMaster.txt"" + ""%%~I"" ""AccReportMaster.txt"" /B"
Print #FileNumber, "PAUSE"
Close #FileNumber
retVal = Shell(MY_FILENAME, vbNormalFocus)


If retVal = 0 Then
MsgBox "An Error Occured"
Close #FileNumber
End
End If


'Added a 1 sec pause time as file was not closing in time before the code tried to delete it
Application.Wait (Now + TimeValue("0:00:01"))
Kill MY_FILENAME


End Sub

Any help would be appreciated...

Thanks

Darren

Hi all,

I'm looking for the best way to transpose the following data.

Original data is the first 2 rows. The number of columns can be anything up to 15-20.

We need the data transposed and to keep the first column data, as per the next 6 rows...
[TABLE="border: 1, cellpadding: 0, cellspacing: 0"]

[TD="width: 64"]Bay3[/TD]
[TD="width: 150"]C1505-808A_Z-23315[/TD]
[TD="width: 150"]C1505-808A_Z-23318[/TD]
[TD="width: 150"]C1505-808A_Z-26330[/TD]
[TD="width: 150"]C1505-808D_Z-26330[/TD]

Bay5

C1505-808B_Z-23315

C1505-808C_Z-23315

.

.

.

Bay3

C1505-808A_Z-23315

Bay3

C1505-808A_Z-23318

Bay3

C1505-808A_Z-26330

Bay3

C1505-808D_Z-26330

Bay5

C1505-808B_Z-23315

Bay5

C1505-808C_Z-23315

[/TABLE]

Would be prefer to do it in VBA, but not essential...

Thanks

Daz