Posts by Gethzerion

Re: Pass Value Of Variable To Another Based On Name

Sounds like a cunning plan.

Many thanks for your time.

I'll attempt to use the search function to find out how to "add" a second hidden column. At the moment I'm populating the combo box using .additem on the initialise of the form.

Re: Pass Value Of Variable To Another Based On Name

As requested - the code:

Const Petty_Cash_Offset As Integer = 17
Const Hospitality_Offset As Integer = 18
Const Transfer_Offset As Integer = 19


Public Sub Add_Trans()


MsgBox "Adding Record", vbApplicationModal


Trans_Date = frm_add_trans.txt_date.Value


If frm_add_trans.rdo_credit = -1 Then
    Trans_Type = "Credit"
Else
    Trans_Type = "Debit"
End If


Trans_Cat = frm_add_trans.cbx_cat.Value


' Need to replace any spaces with underscore and any / with underscore


Trans_Cat = Replace(Trans_Cat, " / ", "_", 1, -1)
Trans_Cat = Replace(Trans_Cat, " ", "_", 1, -1)
Trans_Cat = Trans_Cat & "_Offset"


Column_Offset = Trans_Cat

The cbx refers to a combo box where the user can select the category of the transaction.

I know I could have done this using a select case statement, cycling through the 16 different categories and assigning the value there. What I have attempted to do seemed a little neater.

Hopefully this may clarify

Hi,

Tried searching, got nowhere

Is the following possible?

I have a series of integer constants defined for the column offset to be applied based on the category selected by the user; there are 16 in total.

e.g.

Const Transfer_Offset As Integer = 19

Using concatenate and replace i have a variable named Column_Offset that = the string value of constant variables defined.

e.g. Column_Offset would contain the value Transfer_Offset

what I ams struggling to do, is to get the variable Column_Offset to contain the value of Transfer_Offset i.e 19

Ultimately this value would then be used in an activecell.offset(0, Column_Offset) to move the cell address to the required.

At present, using the above code gives me an error as VBA only goes down one level in the tier of variables and thus tries to offset the active cell by Transfer_Offset, not 19 columns as required.

Hope this makes sense.

Hopefully a simple question.

I have two record navigation buttons on a form. If for example the record number is 1, how do i disable the back button, and if the last record is selected, how do i disable the forward button? If it helpls/hinders, this would also have to work when filters are applied.

Re: Referencing A Daily File

can't check this but

store todays date in a varaible and then using the command dateadd subtract one day.

replace date in the above code with your variable name

Re: Conditional Format Formula using Networkingdays

Hi,

Thanks for your reply, but I copied and pasted your code into Excel and got the following error message:

"You may not use unions, intersections, or array constants for Conditional Formatting criteria"

:crying:

Could you suggest anything else?

Hi Quick question.

In cell A I wish to use a conditional format rule based on the results of subtracting two dates held in rows K and M respectively.

At the moment I use the following formula:

=AND((K14-M14) <= 3,(K14-M14)>= -2)

However, I've noticed a fault in this, and I should be using Networkdays to get the correct value, so I tried:

=AND(NETWORKDAYS(DateValue(K14),DateValue(M14))<=3,NETWORKDAYS(DateValue(K14),DateValue(M14))>=-2)

This gives me an error message saying that I cannot use references to other worksheets or workbooks for conditional formatting.

I could right a macro for this, but I would prefer this to be passive.

Any ideas where I am going wrong?

Re: Exiting A Range to grab additional data

Quote

I can see one problem with your code right away. The For Each...Next construct does not change the active cell automatically, so each time through, your last four lines shown start from wherever the active cell started and keep moving up

Thats what I guessed it to be - thanks for the confirmation

And your code seems to work - but I won't hold that against you

Hi, Is the following possible?

I've set a range as DD14 to G014, then using a For Next Loop asked the program to cycle through each cell in the range.

Upon findng a populated cell, it stores several bits of data from that cell (already done) and then move up one cell to record the cell value (time) and again move one cell up to record the date.

Now I did get this to work using a clunky bit of code that used activecell.offset to move the focus up the two columns and a While .... Wend loop - but using a set range and cycling through each cell it does not appear to be possible - or am I wrong?

Set MyRange(1) = Range("D14:ER14")
Set MyRange(2) = Range("D23:GN23")


For MyCounter = 1 To 2
For Each Cell In MyRange(MyCounter)


If Cell.Value = "" Then GoTo Skip_Cell


MyCell = Cell.Address
MyPosition = InStr(1, Cell.Value, ",")
If MyPosition = 0 And Len(Cell.Value) >= 3 Then
    Tool_Number = Left(Cell.Value, 3)
Else
Tool_Number = Left(Cell.Value, MyPosition - 1)
End If


MyPosition = InStr(1, Cell.Value, "PROD")
If MyPosition > 0 Then
Run_Qty = Trim(Right(Left(Cell.Value, MyPosition - 2), 4))
Run_Qty = Trim(Run_Qty)
Else
Run_Qty = 0
End If


' Ah  what do now?


ActiveCell.Offset(-1, 0).Select
Run_Time = Cell.Value
ActiveCell.Offset(-1, 0).Select
Run_Date = Cell.Value

Using ActiveCell.Formula when equation includes "" [SOLVED]

Many thanks for your help

Hi

How can I get the following to work?

'ActiveCell.Formula = "=IF(ISNA(VLOOKUP(A39,'L-SHA'!$A$2:$C$20,3,FALSE)),"",CONCATENATE(VLOOKUP(A39,'L-SHA'!$A$2:$C$20,3,FALSE)," Masters @ Warehouse"))"

The problem I have is that the IF function I wish to place in the active cell contains speechmarks both for the ISNA & CONCATENATE function, at which point the compiler chucks out an error message saying that it expected the line of code to finish at the first speech mark after the "=If...

Is there anyway round this?

Re: Copy Data from one worksheet to many dependant upon value

Quote

Also you mention that the Paste does not work, what error does it come up with

I know that the syntax is completely wrong, but to answer your question the error message is:

Run Time Error 438

Object doesn't support this property or method

HI

I have an output from another DB system that contains approx 5000 records. These need to be sorted dependant on the value held in column I (not the last populated column). The value in I is a 1,2 or 3 digit code. Dependant on the value in each of the three positions, the record needs to be copied to one or more sheets.

Now I can identify which records need to be copied where by using a series of if statements on left and right funstions - for example:

While ActiveCell.Value <> ""
    If Right(Depot, 1) = "8" Or Right(Depot, 2) = "81" Then
    Selection.EntireRow.Copy
    Sheets("Finished").Select
    Selection.EntireRow.Paste
    Sheets("Raw Data").Select
End If 


ActiveCell.Offset(1,0).Select


Wend

Note that this script does not work (well the paste does not).

So how should I go about this? I am convinced that this is not the best way to do this...

Re: store worksheet name [Solved]

Quote

What are you actually doing?

I'm taking data from a crystal report, and splitting it over several worksheets, re-formatting etc...

Thanks for the help

Re: store worksheet name

Thanks to you both for your quick reposnse.

Spog00 - I must have tried every other possible syntax but that - it works a treat!

Norie - If I use the 'Set' command, can I reference these values through all sub routines??

Quick question.

In VBA how do i store the name of the worksheet that is currently active? Using (and substituting sheet for book) the syntax that I would use for a workbook name does not work

Re: Application Building - VBA or SQL?

Thanks for the responses,

The total number of records is going to be big but not immense, yes it's going to be 10,000+, but given that this should only be needed 6-9 months, it will be somewhere in the region of 10-15k of records. The number of fields required should also be minimal, my rough layout sketch of the access version included a total of 30-35 fields.

At the moment I am looking at what has been asked to be done (with regards to the maths) to see if and how it can be done. The problem comes from when the shape requested to be made out of the master is not uniform.

I agree that the best way, would be to use Excel to do the number crunching, and use access to store the records. My limited experience of trying to shift data to/from access/excel in 97 has shown it to be a very painful process indeed.

Can I just confirm, when you say "use VB" is this Visual Basic - or something else?

*Brain Fade*

Just a quick question, what is the code in VBA equivalent to RoundUp and RoundDown?

Re: Application Building - VBA or SQL?

The more I sat down and thought about this project, the more complex it became. I have no problem with trying to do this myself, but I'd rather start of in "right" package, than do several days work in say access then discover I would have been better off in Excel.

So, one vote for excel, any more...

The title should be Access or Excel, but it won't let me type that ?

OK, I need some advice from experienced application builders. I have been requested to build an application for two users. The main premise is to control stock that is re-sheared down to a new size for 6 - 12 months before a new custom built piece of code is developed and intergrated into another new system probably SAP or SAGE.

At first I thought Access was the way to go, I would have a series of tables that held customer details, the input stock details (in the form of dimensions etc) and the output stock details (again dimensions, and article numbers).

The user would then select a customer code, input stock and output stock and fill the remaining details, such as quantity req'd, in before printing out a cutting note. This data would then be stored in the db, reports would then be issued for stock processed by given time frame, or for a particular article number etc.

Then it all changed. The request was expanded to include a large number of mathematical calculations, from as simple as if one parent makes two child, how many masters will I need to make x children? to including calculations on how many children would I get from this size master (this ain't that simple most masters are rectangular and so are the children, it has to take into account yields if you rotate the master, and/or an increase if you cut one way to beign with, then rotate the offal and cut again). I could go on, but I am sure that you get the picture.

Now, with the increased maths etc. I am not so sure that Excel is not the way forward, maybe using cutom user forms ( I have to do more reading on this matter), lists of data in worksheets etc, and using VBA macros to do the calculations, update lists etc.

The last part to mention is that I have never programmed in Access using either VBA or SQL (outside of copying specific routines from books/websites etc), but then again I have not used UserForms etc in excel....

So what would you suggest - Access or Excel?

My head hurts.... :yikes: :? :yikes: