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.


    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?



    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?


    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.


    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?

    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?



    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?



    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...


    Find and replace code through another code - Solution posted

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

    Re: find and replace code through another code


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

    the thing is in code if say i have


    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


    Re: vba file handling


    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