Posts by Robert B

  • Dynamically Changing Csv File Or Macros

    Re: Dynamically Changing Csv File Or Macros


    Hi


    I am still not entirely clear what the relationship between Column B and Column E is but, if there are a defined number of B values that always have a defined related value then try something like



    This is untested and you will have to enter as many Case statements as there are discreet values for B.


    HTH


    Robert

  • Dynamically Changing Csv File Or Macros

    Re: Dynamically Changing Csv File Or Macros


    Hi


    Welcome to Ozgrid


    This can probably be done, however there are a number of points you would need to clarify first.


    How many values will there be in (e.g.) Column B?
    What is the relationship between those values and the resultant value?
    What is the algorithm you wish to pre-set?


    It would also help if you were to post an example spreadsheet


    Robert

  • Vba Vlookup From Visio

    Re: Vba Vlookup From Visio


    Hi


    try something like


    Code
    Dim myConst As Range, myRange As Range, myValue As Variant, myCol As Integer
Set myConst = Range("B2")
Set myRange = Range("B2", "E4")
myCol = 3




    myValue = Application.VLookup(myConst, myRange, myCol, False)


    Obviously, you will need to change the variables to suit your needs


    HTH


    Robert

  • Looping Through Recordset To Search

    Re: Looping Through Recordset To Search


    Hi


    to answer the second question first, you query a query by selecting the query tab in design view of a select (or any other sort of) query.


    The parameter is passed to the query either by user intervention when the query is executed or, if you are using VB, the parameter will be declared as a variable and a value assigned depending on the structure of the module.


    Perhaps you could post up what you have so far.


    Robert

  • Looping Through Recordset To Search

    Re: Looping Through Recordset To Search


    Hi


    Is what you are looking for a parameter query? This where you create a select query and instead of putting a value in the Criteria section, type in [Enter CustomerID], on execute this will display a message prompting for CustomerID and the chosen customer details will be shown as the result of the query.


    Post back if I am still missing the point


    Robert

  • Looping Through Recordset To Search

    Re: Looping Through Recordset To Search


    Hi


    Welcome to the forum.


    I am a little unsure of what you mean by recordset, is this a table, the result of a query or the outcome of a VBA module. I am also not quite sure what you mean by loop, I assume that, in your example, the CustomerID is unique and any search would return only one value, there then being no need for a loop.


    If you could be a bit more detailed in stating your problem I am sure we can help


    Robert

  • Cell Colour Depending On Value Entered

    Re: Cell Colour Depending On Value Entered


    Hi


    I think there is a limit of 3 conditions using conditional formatting, so VBA is the way to go.


    The other point made by 6strings is valid so you may want to change it to a Sub function and run it whenever it is needed



    Robert

  • Macro For Importing Multiple Variables From Text Files

    Re: Macro For Importing Multiple Variables From Text Files


    Hi


    open the text file as fixed width, putting columns breaks at 9, 16, 50, 57, 61 and 63 and on the next page of the wizard, select 'Do not import' on columns 3, 5 and 7.


    You could then DataFilter on Heat* on column 3 which would then enable the copying of the relevant values.


    The simplest way to automate this task is to record a macro following these steps.


    HTH


    Robert

  • Input Data Using An Input Form

    Re: Im New To Access...help


    Hi


    I assume that your form includes a button for the user to push on completion of data entry, if you look at the properties of this button you will find an OnClick option, you will need to add some code, Query, Macro or Module, to effect the update


    HTH


    Robert

  • Macro For Importing Multiple Variables From Text Files

    Re: Macro For Importing Multiple Variables From Text Files


    Hi


    Welcome to the forum


    I had a quick look at the text file and it appeared that the time and date values always occurred on a line with "REPORT" in the second column. If that is always the case you could simply open the text file, filter on "REPORT", copy the filtered set to a new workbook and, when all text files have been so treated, delete the column containing "REPORT". This leaves the problem of the value relating to "Heat 1 - 4", is that always 0?


    Robert

  • Cleaning Up Code

    Re: Cleaning Code..


    It is also possible that the hard coded Ranges do not contain the esxpected data. It would be more secure to define these as variables and allocate the values at run time


    Robert

  • Customizing Query

    Re: Customizing Query


    Hi


    Replace all the Red Names in the statement below with your names and copy it into the SQL view of a query


    SELECT [[COLOR="Red"]TableX[/COLOR]].[[COLOR="red"]Code[/COLOR]], Min([[COLOR="red"]TableX[/COLOR]].[[COLOR="red"]StartDate[/COLOR]]) AS MinOf[COLOR="red"]StartDate[/COLOR], Max([[COLOR="red"]TableX[/COLOR]].[[COLOR="red"]EndDate[/COLOR]]) AS MaxOf[COLOR="red"]EndDate[/COLOR]
    FROM [COLOR="red"]TableX[/COLOR]
    GROUP BY [[COLOR="red"]TableX[/COLOR]].[[COLOR="red"]Code[/COLOR]];


    This should give you the required query. Let me know how you get on


    Robert