Replies

Not sure where you were expecting to get file size from.
You need in VBA to use the Microsoft Scripting Runtime library.
You can set this in Tools References

Then you can set a scripting filesystem object
and from that get the basic file information.

The function below returns the size in Bytes of the file whose folder and path are passed.

Code:

Function GetFileSize(strFile As String, Optional strFolder As String)

Dim lngFSize As Long, lngDSize As Long
Dim oFO As File
Dim oFD As Folder
Dim OFS As New FileSystemObject

lngFSize = 0

Set OFS = New Scripting.FileSystemObject
If Not IsMissing(strFolder) Then
   If strFolder = "" Then strFolder = ActiveWorkbook.Path
   If Right(strFolder, 1)  "" Then strFolder = strFolder & ""
   If strFile  "" Then
    If OFS.FolderExists(strFolder) Then
        If OFS.FileExists(strFolder & strFile) Then
            Set oFO = OFS.GetFile(strFolder & strFile)
            Set oFD = OFS.GetFolder(strFolder)
            lngFSize = oFO.Size
            lngDSize = oFD.Size
        End If
     End If
    End If
End If

GetFileSize = lngFSize
'You could use lngDSize to return instead the size of the Directory

End Function

Difficult to know without the data.
The formula you have is looking for the value in E41, so IF you want to look for the value in E1 the formula in C1 ought to be

=INDEX(AcType!$B$1:$B$4178,MATCH(TRUE,EXACT(E1,Ac Type!$A$1:$A$4178),0)) (Entered with SHIFT CTRL and ENTER)

This should look for a case sensitive match with the value in E1
Looking for a match in A1:A4178 on the AcType sheet, and bringing back the corresponding value from B1:B4178 on the AcType sheet.

#N/A implies it cannot find a match.

It can be as simple as an extra space in the data in E1

Have a look at the updated attached file.

The module is not quite right as far as I can see.

It ought to be

Code:

Function CaseVLook(compare_value, table_array As Range, _
Optional col_index As Integer = 1)
Dim c As Range
Dim rngColumn1 As Range


Application.Volatile

'This line was wrong
Set rngColumn1 = table_array.Columns(1)
CaseVLook = "Not Found"

'Loop first column
For Each c In rngColumn1.Cells
    If c.Value = compare_value Then
        CaseVLook = c.Offset(0, col_index).Value
        Exit For
    End If
Next c

End Function

Mind you that is for my data below …..

29315-blookup

You can try it with Exact and using Match and Index

29312-caselookup

I have attached an example file

This not the complete answer, but it may give you a possible option.
It uses Match to determine the row position of the text string in the other sheet
It uses the Hyperlink function with the address function to build a Dynamic Link
It does assume that the match is the entire cell content.
There are variations of a solution to show how the formula is built up.

Anyway, it’s in the attached file.
If it helps ok, if not (all knowledge is good)

Formatting the cell as Text causes that problem, rather than curing it.

Thanks Rory.

Interesting, I was just going on hearsay on that one.
That is what they told me, I had not encountered the issue.

I do know that the data for the main bulk is imported,
but the columns that are showing the issue are ones that have been inserted after the import.
I just wonder if they got the wrong end of the stick regarding text.

Infact, thinking ??????????????????????? about it,
I reckon the cells may have been set as text format after the import.
Then new columns inserted are getting the same text format as adjacent ones.
If you change the format back to General it does not change it back to an answer unless you edit the formula.

Hmmmmm.

I will let you know the outcome shortly ……..
And I have just tested that theory and VOILA!

I probably should have looked at the sheet in more detail, but they wouldn’t let me play with it!
AND I was a tad preoccupied at the time with some complex vba coding.

29295-Doh3

Many Thanks for a nudge in the right direction!

Probably not.

It is not my data, and is highly confidential anyway.
AND.. I am not sure I will be allowed a copy to post even if sanitised.
If I can I will upload it.
I am also going to see if it still ocurs in 2010, but I cannot do that until Thu.

I assume these are text string, not real dates.
Do you want to just change the text strings , or convert them to real dates?

If you want to convert the string use a formula

=Mid(CellRef,3,2)&Left(CellRef,2)&Right(CellRef,4)

If You want to convert the string to a real date then use

=Date(Right(CellRef,4),Left(CellRef,2),Mid(CellRef,3,2)) and use a format to show as a date

Where CellRef is the appropriate Cell Reference.

You can change the Font in Custom Header and Footer to use assorted Symbol and Wingdings,
BUT you would need to know the character to use or the ALT key press to create.
It might be easier to copy and paste them from the sheet.

e.g A Book symbol is Wingdings &

I just tested this on my Laptop, running Windows 7 Ultimate and Office 2003, 2007 and 2010.
In all cases it generated the Rank and Percentile data using the Add-In from your 11000+ rows of random data
in 20 to 23 seconds.

That is with 4GB Ram.

Pattern Fills were removed in 2007.

But you could use Andy Popes Add-In Here

If you look at the result of the formula in G9 on your sheet as a number it returns -0.45
Which as Steve points out is a Negative number, because you are using addition and subtraction in your formula,
even though you don’t need negative numbers, you are getting them hence the ################# result.

IF you are saying there will be negatives, but you do not want them, then you will need to use an IF to make sure that they
are eliminated
e.g. =IF(Your Formula <0,"",Your Formula) or equivalent

Also you have a Custom Format of hh:mm;@

If the total time is likely to exceed 24 hours and you want it in hours and minutes
then you should choose to use [h]:mm as the time component.

Whatever you do, you need to stop the negative calculations or you will get ##########.

No worries!
Code was still good, and it allows for xls and xlsx etc!
Although you might be better using InstrRev to search for the . from the Right side
It still returns the position counting from the left, but starts search on the right,
because now file names can have . in the file name
e.g. MyFile.1.1.1.xls

So here, Instr looking for a . would return the value 7
But InstrRev would return 13

To check if file exists

Easiest is probably to use Dir Function

IF Dir(Path & File) = “” then ‘It Does Not exist

The Update below uses this with a Message Box Function to give the user a choice of buttons to press

Code:

Sub Save_to_PDF()
'
' Save_to_PDF Macro
'
Dim strFileName As String, strPath As String, strPDFName As String
Dim intReply As Integer

strFileName = ActiveWorkbook.Name
strPath = ActiveWorkbook.Path & ""
strPDFName = Left(strFileName, Len(strFileName) - 5) & ".pdf"

'Check for File
If Dir(strPath & strPDFName)  "" Then
  intReply = MsgBox("A File named  " & strPDFName & vbLf & "Already exists in Folder" & vbLf & strPath _
           & "Do you want to Replace it?", vbQuestion + vbYesNo + vbDefaultButton1, "File Exists")
  If intReply = vbYes Then
     ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=strPath & strPDFName, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

        MsgBox "Saved To PDF " & strPDFName
  End If
End If

End Sub

Arcturus16a.

The reason for the extra . was I suspect because you are using an .xlsx not an .xls so it needs to crop the 5 characters (see above)
That is why alifrog solution (see above as well) is good because it looks for the .

I’ve not used it, but if I interpret what you want correctly it ought to be a bit like this

Code:

Sub Save_to_PDF()
'
' Save_to_PDF Macro
'
Dim strFileName as String, strPath as String, strPDFName as String

strFileName=ActiveWorkBook.Name
strPath="DriveLetter:FolderSubFolder"    'Put Your Path Here or Use       strPath=ActiveWorkBook.Path & ""
strPDFName=Left(strFileName,Len(strFileName)-4) & ".pdf"

ActiveSheet.ExportAsFixedFormat _
   Type:=xlTypePDF, _
   Filename:= strPath & strPDFName, _
   Quality:= xlQualityStandard, _
   IncludeDocProperties:=True, _
   IgnorePrintAreas:=False, _
   OpenAfterPublish:=False

MsgBox "Saved To PDF"

End Sub