Posts by Sandieslover

    Re: Move multiple rows of data into one row but sorted by columns

    Hi sgdeli!

    Quite a similar threat as your "Offset function copying data from every 30th cell (various rows, various columns)" threat, isn't it? :)
    I think this solution works better. For so many contacts even more formula's wil affect your PC's performance quite drastic!


    Re: Offset function copying data from every 30th cell (various rows, various columns)

    Hi sgdeli,

    tried to replicate a bit of your file in sheet1 and the formulas in sheet 2... works like a charm for me!
    Copied the formula from B1 in to C1, altered the cellreference in"Sheet1!$D$2" and left "D2" out of "(ROW(D2)-1)*30 and then got the result I wanted!
    Did this in the cells A1 ÷ D1 then copy those formulas down for about 15 rows and I got the result I think you want.

    I presume there is a type falure or something in your cell C1.

    However... using this formula for so many cells (about 600 contacts!), I think it will be very slow!

    When there's no typefailure, you could send an example of your file to me. Will take a look then.

    Kind regards,

    Re: I need help matching two columns and deleting other excess files that don't match

    Hi jatokid,

    the only reason I can think of is your Windows/Excel uses another listsepparator as ";". Perhaps an ","
    Try finding out by using the "fx" button infront of the cellvalue/formula row!

    Kind regards,

    p.s. check out your mail

    Re: I need help matching two columns and deleting other excess files that don't match

    Hi I did the job without VBA.
    1st, replace in column A all spaces.
    As the picture filename equals the value of column A addes with ".jpg" I wrote in cell P2 =A2&".JPG" and in cell Q2 the formula =Vlookup(P2;F:F;1;falsh).
    Copy those two cells all the way down and you'll see matches if a picture exists and #N/B if there is no match.
    Then copy column Q to R, pasting it as values. Then you can get rid of the #N/B using the replace command.
    After sorting the file on column R you can delete the rows without a picture easily.

    If someone has a faster VBA solution I would love to hear, but till then I think this does the trick!

    Hi guys,

    Is it possible to read and use the remarks from files stored in a directory, using some code?
    I would like a macro to read this info from 2 pre declared files without opening them and then compare it.

    Many thanx in advance.
    Kind regards,

    Re: Convert workbook to PDF file

    Hi Rune,

    just tried to print a whole workbook with PrimoPDF and it works just fine, I think. I did get a PDF file with all the sheets in it.
    I don't know what version of primopfd I've got, perhaps it's an update thing.
    On the otherhand... did you check 'Whole Workbook' in the "Print what'" section of the print window?


    Solved - Re: Error message on opening a file "Too many different cell formats"

    Hi Andy,

    I already did.
    Before posting I've searched the forum and didn't find anything.
    After posting my threat the post you pointed out to me came up.
    It helped me a little bit.
    Opening the file in a newer excelversion let me recover the data,
    but it will be difficult to sort the info out as everything is in the same, standard format.

    Thanx for you time.

    I'll close this threat.


    Re: can't sum a range

    :roll: And now with the attachment...

    Hi guys and galls,

    I've got the most intriging problem. At least to me it is.

    I've got this file (see attachment) in which I want to summarize a range of cells. Column C in the attachment.
    When the data was entered originally, the table was formatted as TEXT.
    Now I've formatted the cells in columns C and D as numbers, but the range still doesn't summarize.
    However, when I place the cursor inside the cell and leave it without change by press enter, the value inside this cell suddenly is recognized as a number and added to the sum of the range.

    How can I change the cellformat in a way that excel will recognize the data as number?

    Thanx in advance,


    Re: get rid of deleted records [Solved]

    Hi Norie and Ger,

    Norie, I'm sorry I couldn't describe my problem with the right words,
    (probably caused by the fact English isn't my native language),
    but like Ger presumes...I'm forcing the access file in notepad.
    And Ger, the quote in your last reply helped me out...
    Under Tools/Database Utilities I found the option 'Compact and repair Database' which did the trick :)

    :thanx: very much for all your efforts,

    Kind Regards,

    Re: get rid of deleted records

    Hi norie,
    thanx for your reply.
    Sorry for the delayed reaction but I was on a short holiday, last week.

    I get the table in the notepad via "open with..." in windows explorer.
    You'll see a lot of crab, but the records are still recognizable and readable.


    Hi you all,

    as a very unexperienced user of access I want to ask a probably very simple question.
    I've got a database file. After opening it I select the Table tab and open a table. In this table I delete some records by selecting the record(s) and press the button 'Delete Record'. And yes, the record(s) disapear and I'm not able te recover them after saving the file.
    If I, however, open this file in notepad, all of the deleted data still can be read.
    I know from the old DBase program that after deleting records you had to perform a 'purge' to really remove them fom the file.
    How do I do this in Access? With or without code.

    Please help me out.
    Kind regards,

    Re: Show inactive userform [Solved]

    Hi Andy,
    your example works like charm. :thanx: Very much!

    Hi Roy,
    Thanx for your time and answere.
    Andy's example does the trick. For now at least.
    I will save this post for the future and I take a look at John Walkenbach's site.

    Thanx very much,
    and kind regards,

    Re: Show inactive userform

    Quote from Andy Pope

    Maybe using menus on the toolbar would allow you to maintain the grouping of your buttons.

    Hi Andy,

    this is where my inexperience kicks in... :stare:
    Could you give me a small example of what you mean?
    I'm always eager to learn.

    Kind regards,

    Re: Show inactive userform

    Hi guys,

    thanx for your advise!
    If only it where a few buttons it would be better to put the buttons in a toolbar, but as I have several groups of buttons I have made a userform with a Tab for each occasion. Thats the first reason.
    Second... for now toolbars could be the solution, but as I progress in my experience with VBA (witthe help of reading this forum), the manner to do this the way I do now, could be handy in other circumstances.

    So, I appreciate your help very much and hopefully I can return the favour in the future.

    Kind regards,

    Re: Show inactive userform

    Hi RoyUK,
    thanx for your time and reaction!
    Unfortunatly does ShowModal not do the job for me. :(
    ShowModal controls if you have to close the userform or not, before continuing in the worksheet but the userform stays active. If I want to continue working in the sheet via the keyboard, I have to activate the sheet first by clicking the sheet with the mouse pointer first. I want the userform to be on top, but not active.
    Hopefully did I explain myself a bit better and can you help me out.

    kind regards,