Posts by aadarsh

Re: Un-able To Match Data

in the VBA Code, I am simply writing

if Cells (i,"A").Value= SourceValue Then

The SourceValue (of type String) is obtained from a text file.
While Cells(i,"A") refers to the cells which have the apostrophe.

Hi,

I need to compare 2 sets of data, but I have run into a problem- one set of data is prefixed with ' s. This symbol does not appear in the cell, only in the formula box.

For example, S1234 in an excel cell is 'S1234 in the formula box.
Whenever, I compare this cell against the value S1234 and check if they are equal, it returns false.

IS there any way to get around this?

Re: Update Fields From .txt File

i have been able to use this code. however, need help to solve 2 more problems:-

1) what is the code to close a text file?
2) I need to simulatneously open 2 text files and read data from them. is this possible?

Thanks!

Hi,

I need to update some fields in my excel sheet from a .txt file (comma or space separated), based on matching values of a key field in both files. I do not want to import the file into excel and do a vlookup.

I am thinking of a macro that prompts the user for the file location ( txt file) and then scans the txt file and updates the fields.

Would anyone know how to go about doing this or have any code I can use?

Thanks!

Re: Vlookup Return A Formula Instead Of A Value?

can you give an example?

in any case, for a given cell in column 2, how will the formula calculate based on values in column 1?

tks.

Re: Vlookup Return A Formula Instead Of A Value?

Thanks everyone, however, I don't think the solution will work.

Let me try to explain the problem in detail. Sorry, I cannot upload/download sheets.

If Type= "A", Result= Max (22, 2 *(67- Data1))
If Type="B", Result= Round ( 1.7 * Data1)

Type has at least 10 values, which have similar formulas.

And Data1 is not constant, but varies with the row - C1, C2, C3 etc.

Thank you once again.

Hi,

The main sheet on my workbook has 3 fields- 'Type' , 'Data1', 'Result'

For a given row, a certain formula will be applied on 'Data1' to calculate 'Result'. The formula will vary based on the value of 'Type'.

So example if Type = A, Result= Data1*10

Type = B, Result= Data1*20.

The actual formulas are a lot more complicated.

I know this can be done using a nested if statement, but I would like to know if this can be done using Vlookup (where the formula is returned and it is applied on 'Data1')?

I don't mind any other solution as well as long as I don't need to write a long nested if statement!!

Thanks a lot....

Re: Embed Template Within File

Thanks guys, it works perfectly!

Just for my own understanding, if I was to put the button in the menu on the top (with File, Edit, View etc), then does the code have to change significantly?

Re: Embed Worksheet Template Within File

Thanks Tom.

I would prefer the second option. It would be easier for the user. So could you tell me how to add this option into the menu & how is the worksheet stored as an invisible sheet?

Quote from tstom

Yes.

I would store the template worksheet within the selfsame workbook as an invisible worksheet.

Your method of user accessibility is up to you. My opinion? On workbook open, check for the existence of this template in the templates folder. If it does not exist, create it automatically. Now this template is available when the user browses the templates folder.

Another option would be to add a menu item directly below "Insert" such as "Insert New Main". This would insert a copy of the invisible worksheet into the workbook without the need for browsing all available templates.

Which way do you want to go?

Display More

Hi Everyone,

I have a file with two sheets - Main and Reference. The file has a macro that when used from Main sheet displays some data (images to be specific) from the Reference sheet. The main sheet is of a specific format and has some buttons to call the macro.

I want to have the capability to create any number of main sheets of the same format. I know this can be done by saving the Main sheet as a template and right clicking on a sheet and clicking 'Insert..'. But that would require the template to be stored in the excel start file on the local computer.

I want to know if there is any way the worksheet template can be stored within an excel file, so that if the file is sent to people on a different computer, the user can easily add more worksheets using the template?

Thanks.

Hi,

I have a large amount of data (over 50,000 rows) in a single file that I need to divide into multiple smaller files. This is a simplified version of how the main file is:-

Field 1 ,Field 2 ,Field 3 ,Field 4
a ,s1 ,abc , def
b , s1 , ghk , ggh
c , s2 , hhj , tyu
d ,s2 , fhj , uyu
e ,s2 ,hqj , tpu

Based on one of the fields(say field 2), I need all the rows containing the same value of Field 2 to go to a separate file. So in the above case there will be 2 files, one containing all records with Field 2 as s1 and the other containing all records with Field 2 as s2.

Is there any VBA code that can create the required number of files for this purpose?

Thanks,
Aadarsh

Hi,

This problem has been irritating me for days, so would appreciate any help.
This is what i need to do:-

Sheet1 has a number of columns, two of them are 'Name' and 'Signature'.
Sheet2(reference sheet) lists all the names and signatures.
Signatures are all .jpeg files that I have inserted into the relevant cells in sheet2.

I need to do a lookup on sheet2 based on 'Name' and populate the 'Signature' column in sheet1 with the correct signature from sheet2(essentially a vlookup, except that one field is a picture).

I discovered that vlookup doesn't work, but I can copy the cell (including the signature). So I run a macro that clears all the pictures(signatures) in sheet1 and then copies the correspnding signatures from sheet2.

So first of all, is there an easier way to do this as the macro is taking a long time to run?

Secondly, I also need to filter the rows in sheet1. When I filter the rows, the signatures from rows which are not part of the filtered rows are also displayed. I am guessing this happens because the signatures are not referenced exactly to a particular cell. Any way to get around this?

Thanks a lot! I have learnt a lot from this forum already...

Regards,
Aadarsh

Re: Selecting A Worksheet Through A Macro

Hi

In the code, where you have ActiveSheet. replace it wiith Sheets("Your name here")

Aadarsh

Re: Transferring data from xl to word(header)

Hi..Thats what i dd abt a week ago ago and worjks fine now

Thanx neways

Aadarsh

Re: Determining Football Success...

Hi

I think the report can be done easily enough..

Can you attach a copy of the workbook and maybe ill fill in the formulae for youi

Aadarsh

Find and replace code through another code - Solution posted

Hi Barry here is a module i created for making the change

Function replaceincode(wbook As workbook, sourcestring As String, replacestring As String)


Dim s As Variant
Dim t As Variant


For Each ref In wbook.VBProject.VBComponents
    Count = ref.CodeModule.countoflines
    If Count > 0 Then
        b = ref.CodeModule.Lines(1, Count)
        s = Split(b, "" & vbNewLine)
        For i = 0 To UBound(s)
            If InStr(1, s(i), sourcestring,vbTextCompare) Then
                t = Split(s(i), sourcestring,vbTextCompare)
                outcode = t(0)
                For j = 1 To UBound(t)
                    outcode = outcode + replacestring + t(j)
                Next j
            a = ref.CodeModule.ReplaceLine(i, outcode)
            End If
       
        Next i
    End If
Next


End Function

Re: find and replace code through another code

Thanx

Am working on it...will put the code up for reference when i am done

Aadarsh

Re: find and replace code through another code

Hi

Not to be rude, but i know how to access VBE...

the thing is in code if say i have

a="temp"

Say I want to replace 'temp' with another word say 'ace', i would normally go to find and replace in VBA and do it...

Now i want to find and replace this section of cod through another code, rather than manually going to find and replace

Aadarsh

Re: vba file handling

Hi

Attached is a code which does that...not mine found on this forum a while ago
The code allows the user to select a folder and all the files of designated filter are extracted to an array

Filenameslist is the array
You can replace the *.txt, with any file filter
The false or true is for searching subfolders

FileNamesList = CreateFileList("*.txt", False)

This is the code you have to add to a module

Public strFolder As String


'' BIF_Options
Public Const BIF_BROWSEFORCOMPUTER = &H1000
Public Const BIF_BROWSEFORPRINTER = &H2000
Public Const BIF_BROWSEINCLUDEFILES = &H4000
Public Const BIF_BROWSEINCLUDEURLS = &H80
Public Const BIF_DONTGOBELOWDOMAIN = &H2
Public Const BIF_EDITBOX = &H10
Public Const BIF_NEWDIALOGSTYLE = &H40
Public Const BIF_RETURNFSANCESTORS = &H8
Public Const BIF_RETURNONLYFSDIRS = &H1
Public Const BIF_SHAREABLE = &H8000
Public Const BIF_STATUSTEXT = &H4
Public Const BIF_USENEWUI = &H40
Public Const BIF_VALIDATE = &H20
Public Const BIF_NONEWFOLDERBUTTON = &H200


'// Minimum DLL version shell32.dll version 4.71 or later
'// Minimum operating systems   Windows 2000, Windows NT 4.0 with Internet Explorer 4.0,
'// Windows 98, Windows 95 with Internet Explorer 4.0
'// objFolder = objShell.BrowseForFolder(Hwnd, sTitle, BIF_Options [, vRootFolder])


Public Function BrowseForFolderShell( _
    Optional Hwnd As Long = 0, _
    Optional sTitle As String = "", _
    Optional BIF_Options As Integer = BIF_VALIDATE, _
    Optional vRootFolder As Variant) As String


Dim objShell As Object
Dim objFolder As Variant
Dim strFolderFullPath As String


Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.BrowseForFolder(Hwnd, sTitle, BIF_Options, vRootFolder)


If (Not objFolder Is Nothing) Then
    '// NB: If SpecFolder= 0 = Desktop then ....
    On Error Resume Next
    If IsError(objFolder.Items.Item.Path) Then strFolderFullPath = CStr(objFolder): GoTo GotIt
    On Error GoTo 0
    '// Is it the Root Dir?...if so change
    If Len(objFolder.Items.Item.Path) > 3 Then
        strFolderFullPath = objFolder.Items.Item.Path & Application.PathSeparator
    Else
        strFolderFullPath = objFolder.Items.Item.Path
    End If
Else
    '// User cancelled
    GoTo XitProperly
End If


GotIt:
BrowseForFolderShell = strFolderFullPath


XitProperly:
Set objFolder = Nothing
Set objShell = Nothing


End Function


Sub BrowseFavorites()


'// Using String
'// This will not only limit the User to a specific Folder
Dim strFolder As String
Dim objShell As Object
Dim objFolder As Variant
Dim strFolderFullPath As String


On Error Resume Next
Set objShell = CreateObject("Shell.Application")
'Set objFolder = objShell.Namespace(&H6)


Set objFolder = objShell.BrowseForFolder(0, "", BIF_BROWSEINCLUDEFILES, &H6)
On Error GoTo 0


If (Not objFolder Is Nothing) Then
    '// NB: If SpecFolder= 0 = Desktop then ....
    On Error Resume Next
    If IsError(objFolder.Items.Item.Path) Then strFolderFullPath = CStr(objFolder): GoTo GotIt
    On Error GoTo 0
    '// Is it the Root Dir?...if so change
    If Len(objFolder.Items.Item.Path) > 3 Then
        strFolderFullPath = objFolder.Items.Item.Path & Application.PathSeparator
    Else
        strFolderFullPath = objFolder.Items.Item.Path
    End If
Else
    '// User cancelled
    GoTo XitProperly
End If


GotIt:
'strFolderFullPath


XitProperly:
Set objFolder = Nothing
Set objShell = Nothing




If strFolderFullPath = vbNullString Then
    MsgBox "You cancelled"
Else
    MsgBox strFolder
End If


End Sub


Private Sub btnGetDetailsOf_Click()
    
Dim objShell  As Shell
Dim objFolder As Folder


Set objShell = New Shell
Set objFolder = objShell.Namespace("C:\WINDOWS")
    
If (Not objFolder Is Nothing) Then
    Dim objFolderItem As FolderItem
    Set objFolderItem = objFolder.ParseName("clock.avi")
   
    If (Not objFolderItem Is Nothing) Then
        Dim szItem As String
        szItem = objFolder.GetDetailsOf(objFolderItem, 2)
    End If
        
    Set objFolderItem = Nothing
End If
    
Set objFolder = Nothing
Set objShell = Nothing


End Sub


Function CreateFileList(FileFilter As String, _
    IncludeSubFolder As Boolean) As Variant
     
     ' returns the full filename for files matching
     ' the filter criteria in the current folder
    
Dim FileList() As String, FileCount As Long
CreateFileList = ""
Erase FileList
If FileFilter = "" Then FileFilter = "*.*" ' all files
Debug.Print CurDir
strFolder = BrowseForFolderShell(, , , 0)
If strFolder = "" Then
    MsgBox "You Cancelled"
    Exit Function
End If
With Application.FileSearch
    .NewSearch
    .LookIn = strFolder
    .fileName = FileFilter
    .SearchSubFolders = IncludeSubFolder
    .FileType = msoFileTypeAllFiles
    If .Execute(SortBy:=msoSortByFileName, _
        SortOrder:=msoSortOrderAscending) = 0 Then Exit Function
        
    ReDim FileList(.FoundFiles.count)
    For FileCount = 1 To .FoundFiles.count
        FileList(FileCount) = .FoundFiles(FileCount)
    Next FileCount
    .FileType = msoFileTypeExcelWorkbooks ' reset filetypes
End With
    
CreateFileList = FileList
Erase FileList


End Function

Hi

Dont think can be done, but no harm trying

Is there any way i can write a code, which searches through the code of another file, finds a keyword, and replaces by another

Please note:I want to search the code not the excel file.

Aadarsh