DaiMitnick's Feed - Programmer's Heaven

Re: Help with DIM and using

Wed, 04 May 2011 02:06:06 -0700

Posted a 'reply on the VBA forum.

How can you have done so much coding without relying on variables? Bizarre. Well I'm guessing it's too late now, but for reference's sake, this is how you declare and assign a value to a variable in VBA:

Dim TheString As String
TheString = "01"

More commonly you may want this variable to be something the user enters, which would be:

TheString = InputBox("Input the number here: ")

Then to use it:

.SourceDataFile = "U:\POLL\" & TheString & "\misc.dbf"

Hope this helps someone, if not you. Regards, Dai

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: Placing cursor in datagridview cell

Wed, 04 May 2011 01:40:36 -0700

Posted a 'reply on the Advance Visual Basic forum.

Well the simplest solution I can see would be to after sending the command:

SendKeys.Send("{TAB}")

Send another for the home key:

SendKeys.Send("{HOME}")

Should work, let me know if not. Regards, Dai

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: Reference Variable Row Numbers in Macro

Wed, 13 Apr 2011 02:13:30 -0700

Posted a 'reply on the VBA forum.

Hi Dragonfyre, I don't understand what you mean by reference the cells, do you wish to do something with the values in those rows, or simply select that range?

The example below assumes the department names are in column A, and values in column B, it would go through all the rows, total up the values in column B and at the end select the range of values in column B,if it finds no matches, will messagebox to say so. Just adapt to your needs:

Sub Macro1()

Dim strDept As String
strDept = InputBox("Gimme a department name!")
Dim iRow As Integer, SumTotal As Double, FirstRow As Integer, LastRow As Integer
iRow = 2
FirstRow = 99999
LastRow = 0
Dim boolFound As Boolean
boolFound = False
Do
    If Cells(iRow, 1).Value = strDept Then
        boolFound = True
        If iRow < FirstRow Then FirstRow = iRow
        If iRow > LastRow Then LastRow = iRow
        SumTotal = SumTotal + Cells(iRow, 2).Value
    Else
        If Cells(iRow, 1).Value = "" Then GoTo NoneFound
    End If
    iRow = iRow + 1
Loop Until Cells(iRow, 1).Value <> strDept And boolFound = True

'Do something here with either the total values, or select the range
'To select the range in column B:
Range("B" & FirstRow & ":B" & LastRow).Select
Exit Sub

NoneFound:
MsgBox ("No values found for department " & strDept)
End Sub

HTH, Dai

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: Help VBA for EXCEL

Wed, 13 Apr 2011 01:48:53 -0700

Posted a 'reply on the VBA forum.

Hi scorpion,

Well that code still gives you the last occurrence of any value in that column, which is what you'll need if you're trying to select all the values you have, it just means you'll have blank cells in between, so just ensure your code ignores them (If Cells(i,1).Value = "" Then), if that's what you need to do, just adapt to your code where necessary.

As to the last line, the problem here is you're referring to constants rather than writing a formula, there is no H3 constant, if you want to refer to the range, you must use the range or cells function, if you want to input a formula then put it in quotations. Also, you don't need to use the .formula if you're just inserting or extracting flat values. So, you can change this:

Range("H4").Formula = GrNo
Range("H5").Formula = 0.123 * H3
Range("H6").Formula = GrNo - H5

Into this (flat values):

Range("H4").Value = GrNo
Range("H5").Value = 0.123 * Range("H3").Value
Range("H6").Value = GrNo - Range("H5").Value

Or this (formulae in the worksheet):

Range("H4").Value = GrNo
Range("H5").Formula = "=0.123 * H3"
Range("H6").Formula = "GrNo - H5"

Hope this sets you up to finish it off now, don't be afraid to ask if you are stuck though. Regards, Dai

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: Help VBA for EXCEL

Thu, 07 Apr 2011 08:15:41 -0700

Posted a 'reply on the VBA forum.

To answer your first question, assuming you have no gaps in column A, you can find out how many rows there are using the following code:

Dim LastRowColA As Integer
LastRowColA = Range("A3001").End(xlUp).Row

With the function, there's a little misunderstanding here. That function is not a UDF (User Defined Function) that can be called from a cell. This is a normal function that has to be called from another sub or function. If you look at one of the earliest posts, you will see a sub called DisplayAverages, just below the code for the function, this is an example of how you can call the function to return the results.
The function returns an array, containing the different results. Note that the worksheet parameter you pass in has to be an actual Worksheet object, not just the name of the sheet, which is one reason why it will not work when called from a cell.
As for the averages you place in column A, I still would recommend putting them somewhere else, to save confusion and improve data quality, however if you do need to leave them there, you can just edit the function to ignore any of those rows. You could do this by changing the following lines:

For i = 1 To NumberOfValues
    GetRow = Round(Rnd() * iRow, 0)

To:

For i = 1 To NumberOfValues
TryAgain:
    GetRow = Round(Rnd() * iRow, 0)
    If GetRow Mod 31 = 0 Then GoTo TryAgain

HTH, Dai

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: Decimal to Binary Converter

Tue, 05 Apr 2011 02:50:28 -0700

Posted a 'reply on the VBA forum.

My guess would be this line:

value = value / 2

And the one similar to it, value is a short, which is a type of integer, which cannot have decimal places, so if value is an odd number, when divided it will try to create a decimal into the short and will just simply fail. Try changing it to double and see if that helps. HTH, Dai

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: String Manupilation

Tue, 05 Apr 2011 02:45:15 -0700

Posted a 'reply on the VB.NET forum.

Hi Duck, first off, there are a few spelling and syntax errors in there so keep an eye out for that. You don't need to involve chars so heavily, the below codes will do what you're looking for. HTH, Dai

Reverse string:

        Dim StrStart As String = "123456"
        Dim StrEnd As String = ""

        For Each Ka As Char In StrStart
            StrEnd = Ka & StrEnd
        Next
        Debug.Print(StrStart & " - " & StrEnd)

String +10:

        Dim Str1 As String = "ABC", Str2 As String = ""
        Dim intChar As Integer
        For Each Ka As Char In Str1
            intChar = Asc(Ka) + 10
            Str2 &= Chr(intChar)
        Next
        Debug.Print(Str2)

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: UPDATE MySQL using VB.NET

Tue, 05 Apr 2011 02:17:50 -0700

Posted a 'reply on the VB.NET forum.

Because you are trying to change values in different columns, based on the values in other various columns, you may find the way you have now is the simplest, however you can use some case statements to test if it's any quicker, something like below.

UPDATE categories
    SET display_order = CASE id
        WHEN 1 THEN 3
        WHEN 2 THEN 4
        WHEN 3 THEN 5
    END,
    title = CASE id
        WHEN 1 THEN 'New Title 1'
        WHEN 2 THEN 'New Title 2'
        WHEN 3 THEN 'New Title 3'
    END
WHERE id IN (1,2,3)

Go to this site to understand it, it is where I took above code from:

http://www.karlrixon.co.uk/articles/sql/update-multiple-rows-with-different-values-and-a-single-sql-query/

HTH, Dai

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: VBA Code (Arrays) to Print PDF's from Excel

Tue, 05 Apr 2011 02:04:28 -0700

Posted a 'reply on the Visual Basic forum.

Well my first thought would be to use Word to create a document (template) and link that to Excel with a mail merge, will do what you need. If you are doing it in Excel, I would use the indirect formula to show the data depending upon what row you select. for instance if you have a number between 1 and 100 in cell A1 of worksheet2, to pull out what's in coolumn A you would use:

=INDIRECT("Sheet1!A" & $A$1)

You can easily change the number through code then. As for the PDFs, best way is to get yourself a PDF printer, like CutePDF or similar, preferably one which has an option to not ask for a save location on each file, otherwise you can't fully automate it (you'll sit there clicking OK 100 times). It's easy enough to get VBA to print to the active printer, or even to change printer then print, just record a macro of yourself changing to the PDF printer and printing, use the code it produces. HTH, Dai

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: Please help me figure out the problem in My Formula

Tue, 05 Apr 2011 01:48:07 -0700

Posted a 'reply on the VBA forum.

Could you be more specific about what doesn't work, does it stop on a line asking you to debug, or does it run and you find the outcome isn't as expected?

The quotes you have are fine, the only things I can see that would cause a problem here, is either you are trying to insert an invalid formula (i.e. if say you put a formula into B7 that does a countif on previous 10 rows, it will fail), or perhaps the last line, if you are looking to just go to the next line, you need to remove the Myrow bit, i.e. should look like:

ActiveCell.Offset(1).Select

HTH, Dai

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: Help VBA for EXCEL

Tue, 05 Apr 2011 01:30:38 -0700

Posted a 'reply on the VBA forum.

OK, I was just about to answer your questions, had some good answers ready too, shame. Well the 30 random values thing was given a while ago, you have changed many things in your workbook since, so perhaps is no longer fit for purpose, may need a little editing, should work though.

What code are you using to call the function? Post it please. Also, try to figure out where it falls over, to do this put in: Debug.Print "Step 1", and step 2 etc. about every three lines (not inside loops) and when you next check the VBE, you will be able to see at which step it stopped.

Without some code to look at I can't tell you why it doesn't work, as it works fine for me. Regards, Dai

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: Caculation

Mon, 28 Mar 2011 11:50:13 -0700

Posted a 'reply on the VB.NET forum.

Try changing it to double.

D

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: Caculation

Mon, 28 Mar 2011 09:21:19 -0700

Posted a 'reply on the VB.NET forum.

Hi Young, yes this Dim you speak of is the way in which you declare variables. You do Dim <variable name> As <variable type>. You can then assign values to these variables and perform calculations on them. For instance your code would need to be something like (but more added):

Dim intWidth as Integer
Dim intHeight as Integer
Dim intResult as Integer

IntWidth = 50
IntHeight = 100
IntResult = (IntWidth * IntResult) / 2

MsgBox(IntResult)

This would display a message simply saying 2500.
For more on variables take a look at:
http://visualbasic.about.com/od/quicktips/qt/vardeclare.htm
And any other beginner sites for VB.NET

HTH, Dai

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: Loops

Mon, 28 Mar 2011 08:55:05 -0700

Posted a 'reply on the VB.NET forum.

This is a very easy one to code for, but if I give you the code you won't learn anything. What you will need is an inputbox to record the value the user gives, if you have a variable called 'TheNumber', just type into visual studio,

TheNumber = InputBox(

You will then work the rest of that out for yourself. Then use a simple IF statement, If TheNumber >=2 And..." if it is false you can MsgBox the text to say they did it wrong, if true then you start your loop. There are different ways to loop but easiest here is a for loop, i.e.

For LoopNumber = 852 to 963
'Do what you must
Next

You have all the bits of code you will need there, so just adjust the numbers and other parts to suit your scenario and you're done!

HTH, Dai

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: Help VBA for EXCEL

Fri, 25 Mar 2011 10:37:47 -0700

Posted a 'reply on the VBA forum.

Not necessarily too much to do, but as you are finding the last row each time to populate it, any overlap could cause values to be overwritten incorrectly.

I would create a separate worksheet for each scale, so that GetSWDataCom11 points to sheet "Scale1" etc. You can always compile all values into one sheet if necessary.

Try this, if it does not work, we at least limit the possible causes. Did you change the code so that the average goes on to the next row or perhaps a different column? Once you have tried this, if it fails include your current code in your next post. Regards, D

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: Help VBA for EXCEL

Thu, 24 Mar 2011 10:19:57 -0700

Posted a 'reply on the VBA forum.

How do you mean simultaneously? Separate scales are all feeding into the same workbook at the same time are they?

As you are finding some aberrations (outside the 50-150%) are slipping through and some normal values are being deleted, it would seem likely that this simultaneous running is causing it to delete at the wrong time(or place). What way are you getting these 10 simultaneous balances? (Does the macro pull through 10 values each time, or are you calling the macro 10 times?) Regards, D

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: Macro to read list and fill binary matrix

Thu, 24 Mar 2011 08:33:32 -0700

Posted a 'reply on the VBA forum.

Hi Chris, I think you may find it easier to simply use countif formulae and use the macro to apply the formula and copy-paste the values. However, as you already have done some work, you could just edit that sub, all you really need is to move away from using column letters and start using cell references. For instance the following code:

For Each obCurCell In Range("D371:D372")
    For Each obCurCell2 In Range("C3:C367")
        If obCurCell.Value = obCurCell2.Value Then
            loX = obCurCell2.Row
            Range("D" & loX) = 1
        End If
    Next
Next

Could be changed to:

Dim i As Integer, j As Integer
For i = 371 To 372
    For j = 3 To 367
        If Cells(i, 4).Value = Cells(j, 3).Value Then
            Cells(j, 4).Value = 1
        End If
    Next
Next

Then it's a simple case of changing those hard-coded numbers to variables too, i.e. instead of that 4 there, change it to a variable called k (or whatever you like) and put in another for loop of "for k = 4 to 63" (columns D to BK). HTH, if you have any questions, please ask. Regards, Dai

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: HELP!!!!!!!!!!!!!!Counting Var / Number

Tue, 22 Mar 2011 11:19:35 -0700

Posted a 'reply on the Pascal forum.

EDIT: Ignore post, seems Carl addressed these issues in the time I was away from my PC.

Haven't done Pascal in a few years so forgive my ignorance. First, is this line:

readln(posi);posit:=posi;

actually part of the if statement? To me it looks like it will be run whether the number is positive or not. Whether it is or isn't it looks like you're setting it to 0 each time, because of this line:

posi:=0; negi:=0; zero:=0;

As I said, I'm rusty so perhaps I'm wrong. Also, where are posit, zerot and negit declared, because if they are not global then (I believe) they will go out of scope each time you call the procedure and so it will not return any values to your main procedure.

Regards, Dai

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: VBA copy paste

Tue, 22 Mar 2011 10:48:36 -0700

Posted a 'reply on the VBA forum.

OK first off, assigning a macro...

In Excel go to Insert>Shapes and pick a shape and draw it somewhere
Right-click the shape you have drawn and you will see an 'Assign a Macro' option, click that and choose your macro from the list, click OK
If you want a keyboard short-cut instead or as well, in Excel hit ALT+F8, this will bring up the Macros window, can also be found under Tools>Macros>Macros in 2003 and earlier or View>Macros in 2007 onwards
Click the 'Options' button and assign whatever key you want to use to it, then click OK
Note, most Ctrl shortcuts already have a function, e.g. Ctrl+A is select all, Ctrl+P is print, Ctrl+X, Ctrl+C, Ctrl+V are cut, copy, paste respectively, and the list goes on, so I recommend you don't use any of these commands, I would use Ctrl+Shift+(Any key).

As for the separating of the suppliers, you will need to build a list of what relates to supplier 1 and supplier 2, then use that list to find which one the current row relates to. This example assumes it's the Company name (Column B) that decides which it belongs to:

Sub UpdateSuppliers()
Const Supplier1Companies as String = "{ABC Ltd}{MNO Ltd}{XYZ Ltd}"
Const Supplier2Companies as String = "{DEF Ltd}{PQR Ltd}{XYZ Ltd}"

Dim iRo As Integer, NumCols As Integer, NumRow1 As Integer, NumRow2 As Integer
Dim CompSht As Worksheet, Sup1Sht As Worksheet, Sup2Sht As Worksheet
Set CompSht = Sheets("CompanyA")
Set Sup1Sht = Sheets("Supplier1")
Set Sup2Sht = Sheets("Supplier2")
NumRow1 = Sup1Sht.Cells(Sup1Sht.Rows.Count, 1).End(xlUp).Row + 1
NumRow2 = Sup2Sht.Cells(Sup2Sht.Rows.Count, 1).End(xlUp).Row + 1
If ActiveWorkbook.ActiveSheet <> CompSht Then Exit Sub
iRo = ActiveCell.Row
NumCols = 6

If InStr(1, Supplier1Companies, "{" & CompSht.Cells(iRo, 2).Value & "}") > 0 Then
    For i = 1 To NumCols
        'Input data...
        Sup1Sht.Cells(NumRow1, i).Value = CompSht.Cells(iRo, i).Value
    Next
End If

If InStr(1, Supplier2Companies, "{" & CompSht.Cells(iRo, 2).Value & "}") > 0 Then
    For i = 1 To NumCols
        'Input data...
        Sup2Sht.Cells(NumRow2, i).Value = CompSht.Cells(iRo, i).Value
    Next
End If

End Sub

That should do the trick. If in this example you had ABC Ltd on the line, it would go only to Supplier 1 sheet, if you had DEF Ltd, will only go to Supplier 2 sheet, if you had XYZ Ltd, it would go to both. As always, ask if you need to follow up on any of this.

Regards, Dai

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: Help VBA for EXCEL

Tue, 22 Mar 2011 10:16:49 -0700

Posted a 'reply on the VBA forum.

OK I think I'm getting a clearer picture now, so the GetSWDataCom14 sub gets called each time something is weighed correct? And WData is the actual weight being pulled from the Com14 program.

So if the weight is below 75% of the initial inputbox value (i.e. GrNom4) or above 175% (or 50 and 150, whatever) then you delete the value completely as if it had never been weighed?

Then if they weigh something that's within these bounds (75% - 175%), but looks too high for the operator, e.g. 30.27, the operator will then weigh a 23 gram weight on the scale as a way of informing the computer that the last value was too high or low, correct?

If yes to all 3 above, then all you need to do is change the following code:

ThisWorkbook.Sheets("Sheet1").Cells(RowPtr, 1).Formula = WData
If (WData < 0.75 * GrNom4 Or WData > 1.75 * GrNom4) And WData <> 23 Then ThisWorkbook.Sheets("Sheet1").Cells(RowPtr, 1).Value = ""

To this:

ThisWorkbook.Sheets("Sheet1").Cells(RowPtr, 1).Formula = WData
If WData = 23 Then ThisWorkbook.Sheets("Sheet1").Cells(RowPtr -1, 2).Value = 23
If WData < 0.75 * GrNom4 Or WData > 1.75 * GrNom4 Or WData = 23 Then ThisWorkbook.Sheets("Sheet1").Cells(RowPtr, 1).Value = ""

My only worry is that you are overwriting the weight with the average formula every 31 rows, are you sure you want to overwrite it, not put it somewhere else?

Just thinking if you get say a 30.28, on the 31st row, or 62nd etc, then you won't even know about it as it'll just be overwritten with a formula for the averages. You could simply put it on the next line instead so nothing is overwritten?

Also, I'd recommend working out the average in the code itself, then insert the value into the cell rather than a formula, helps speed up Excel if you use less formulae, may not be noticeable on a small workbook, but as it grows you may find it slow down a little.

Anyway I hope I have given what you need, if not, please do ask, that's what I'm here for, to answer the sort of questions I was asking everyone else 5+ years ago.

Regards, Dai

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: How do i assign keys (I´m a beginner)

Tue, 22 Mar 2011 03:32:54 -0700

Posted a 'reply on the Game programming forum.

I would suggest to overcome this problem, and to stop your game having problems later down the line (mainly flickering) that you abandon the pictureboxes now.

You should learn to draw directly on the form, it's not just a lot quicker and more reliable than using pictureboxes, but it also allows you to use the form_keypress event for your controls.

I would recommend reading the small article in the first link to understand the basis of how it works, then use the code in the 2nd link to help guide you to drawing full pictures directly to the form.
HTH, Dai

http://www.ww.functionx.com/bcb/gdi/drawing101.htm

http://www.codeproject.com/KB/graphics/cpicture.aspx

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: Need help changing the color of background

Tue, 22 Mar 2011 03:17:55 -0700

Posted a 'reply on the VB.NET forum.

The background of the button or the form? Well here's both:

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Button1.BackColor = Color.DarkSalmon
        Me.BackColor = Color.Aquamarine
    End Sub

So if you create a button called Button1, this code will change the button's colour to DarkSalmon and the form itself to Aquamarine, just play around and edit as necessary. Beware though, if you have 3d-style buttons (the default), you will notice that the border and the edge just inside the border will not change colour, if you need to colour the whole button you can set it's "FlatStyle" property to "Flat" and use the code below in addition to changing the backcolour.

Button1.FlatAppearance.BorderColor = Color.DarkSalmon

HTH, Dai

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: Progress menu with popup?

Mon, 21 Mar 2011 05:25:01 -0700

Posted a 'reply on the VB.NET forum.

It keeps opening because each time the timer ticks, it is finding that the progress bar is full and therefore opens a messagebox as instructed. You need to either stop the timer once complete, or use a public variable (boolean is best) to tell it when to stop. So you could use this:

 
    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
        ProgressBar1.Increment(10)
        If ProgressBar1.Value = 100 Then
            Timer1.Stop()
            MsgBox("Loading complete.")
        End If
    End Sub

Or this:

    Public boolFin As Boolean

    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
        If boolFin = False Then ProgressBar1.Increment(10)
        If ProgressBar1.Value = 100 Then
            If BoolFin = False Then MsgBox("Loading complete.")
            BoolFin = True
        End If
    End Sub

HTH, Dai

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: I am facing problem with my css design ???

Mon, 21 Mar 2011 05:14:08 -0700

Posted a 'reply on the HTML & WEB-Design forum.

I don't see what's wrong, all I can see is that in Chrome, only the top part (content div) centers, in IE it all centers except the subheader, if this is all the problem is, you just need to add some center tags to the div, or remove them if you want it left-aligned. If this isn't the problem, be more specific. Regards, Dai

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: Sub Rutine

Mon, 21 Mar 2011 04:59:05 -0700

Posted a 'reply on the Visual Basic forum.

The only thing I can think of is that the public variable is within a different class and perhaps you are not referring to it properly. Without seeing the code, I'm afraid I can't help any more. Dai,

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: VBA copy paste

Mon, 21 Mar 2011 04:55:08 -0700

Posted a 'reply on the VBA forum.

OK well going on the assumption that the other sheets have the same headers, and you wish to enter the same data into both, you could use something like this:

Sub UpdateSuppliers()
Dim iRo As Integer, NumCols As Integer, NumRow1 As Integer, NumRow2 As Integer
Dim CompSht As Worksheet, Sup1Sht As Worksheet, Sup2Sht As Worksheet
Set CompSht = Sheets("CompanyA")
Set Sup1Sht = Sheets("Supplier1")
Set Sup2Sht = Sheets("Supplier2")
NumRow1 = Sup1Sht.Cells(Sup1Sht.Rows.Count, 1).End(xlUp).Row + 1
NumRow2 = Sup2Sht.Cells(Sup2Sht.Rows.Count, 1).End(xlUp).Row + 1
If ActiveWorkbook.ActiveSheet <> CompSht Then Exit Sub
iRo = ActiveCell.Row
NumCols = 6

For i = 1 To NumCols
    'Input data...
    Sup1Sht.Cells(NumRow1, i).Value = CompSht.Cells(iRo, i).Value
    Sup2Sht.Cells(NumRow2, i).Value = CompSht.Cells(iRo, i).Value
Next
End Sub

Add a button to your first sheet (or better still assign a keyboard shortcut) to this macro. When run, it will take the row you are currently on and input the data into the other 2 sheets. This is a very basic macro, if you would want it to only put it to one of the sheets, based on the company for example, or any other intricacies, can easily be done with some if statements. If you want to know how to do something extra, just ask. HTH, Dai

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: Help VBA for EXCEL

Mon, 21 Mar 2011 04:28:59 -0700

Posted a 'reply on the VBA forum.

Sorry I haven't replied, do not tend to check over the weekend.

OK, I think I now know what you mean, I was a bit thrown because I thought you were only looking to do this to randomly selected numbers, but if you are looking to do it to the whole data, I'm thinking you would need to use something like the following:

Global GrNom4 As Single

Sub Write50s()
Dim iRo As Integer, NumRows As Integer, MaxValue As Double, MinValue As Double, OrigValue As Double
Dim Msg50 As String
Dim TheSht As Worksheet
Set TheSht = Sheets("Sheet1")
NumRows = TheSht.Cells(3000, 5).End(xlUp).Row
Msg50 = "Err 50: Invalid Value"
GrNom4 = InputBox("nnnnnnn&mmmmmmmm XX:")
MaxValue = 1.5 * GrNom4
MinValue = 0.5 * GrNom4

TheSht.Columns(6).ClearContents 'Clears column F before starting
For iRo = 1 To NumRows
    'If Column E has a value greater than the maximum allowed...
    If TheSht.Cells(iRo, 5).Value > MaxValue Then TheSht.Cells(iRo, 6).Value = Msg50 & " (too high)"
    
    'If Column E has a value smaller than the minimum allowed...
    If TheSht.Cells(iRo, 5).Value < MinValue Then TheSht.Cells(iRo, 6).Value = Msg50 & " (too low)"
    
Next
End Sub

This is based on columns E and F again, if you wish to put the "50" code in column A, change the 6s to 1s. Hope this really does do what you need, if not, just ask again, maybe cite some more examples, We WILL get there! Regards, Dai

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: List of ALL Controls in PROJECT - VB.net

Fri, 18 Mar 2011 06:01:04 -0700

Posted a 'reply on the VB.NET forum.

That's strange, I used the link command and that actually stopped the link displaying, never mind, have pasted the link text again. Regards, Dai

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: ADO.Net DBGRID

Fri, 18 Mar 2011 03:42:16 -0700

Posted a 'reply on the Advanced VB.Net forum.

Could you give any code?

How are you doing it at the moment, are you making the datagrids connect directly to tables in Sql Svr, or are you sending the outputs of the query into a dataset then binding that to the datagrid?

Can you not just use a join query, with some group by clauses to achieve what you need?

Sorry for all the questions, just hard to help without knowing more or seeing what you're trying to achieve exactly. Regards, Dai

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: Need help with visual basic please

Fri, 18 Mar 2011 03:33:15 -0700

Posted a 'reply on the VB.NET forum.

You don't really need the checkbox_changed event, though I would leave in the first bit that disables the button, doesn't hurt, just delete the rest. You'll need to use the button click event, something like this should suffice:

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Const ErrMessage1 As String = "Error: You have not entered any text"
        Const ErrMessage2 As String = "Error: You have not ticked the box"
        If String.IsNullOrEmpty(TextBox1.Text) Then GoTo Err1
        If CheckBox1.Checked = False Then GoTo Err2
        'If you have made it this far there are no errors, so run code...
        RunRoutineThatHappensAfterClickingTheButton()
        Exit Sub
Err1:
        MsgBox(errmessage1)
        Exit Sub
Err2:
        MsgBox(ErrMessage2)
    End Sub

By the way, you may want to use a timer and public variables to know when to change the progress bar. Hope this helps, Dai

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: List of ALL Controls in PROJECT - VB.net

Thu, 17 Mar 2011 05:19:29 -0700

Posted a 'reply on the VB.NET forum.

Yes, getting the controls in VB.NET is a very tricky thing indeed, one of the few disadvantages of .NET compared to Classic. The following page gives one of the easiest methods I've found for doing it:

http://www.codeproject.com/KB/vb/control_arrays_in_vbnet.aspx

Hope this will help you to achieve what you need. Dai

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: Help VBA for EXCEL

Thu, 17 Mar 2011 04:48:53 -0700

Posted a 'reply on the VBA forum.

Not 100% sure I understand what you need, but my guess is that going down column E you have some blank values and/or zeros, you wish to ignore these and only put a 50 by the last real number when a 50 is found, so if going down column E you had the values:
23, 44, 50, 67, 0, 0, 50, 55, ""(blank), 50, 616

You would want 50s to appear in Column F along side the 44, 67 and 55.

If that is correct then the below code will achieve what you want. If it is not, please reply back with another explanation of what you're looking for.

Sub Write50s()
Dim iRo As Integer, NumRows As Integer, LastOcc As Integer
Dim TheSht As Worksheet
Set TheSht = Sheets("Sheet1")
NumRows = TheSht.Cells(3000, 5).End(xlUp).Row
LastOcc = 1
TheSht.Columns(6).ClearContents 'Clears column F before starting
For iRo = 1 To NumRows
    'If Column E has a 50, last occupied cell gets a 50 in Column F
    If TheSht.Cells(iRo, 5).Value = 50 Then TheSht.Cells(LastOcc, 6).Value = 50
    
    'If Column E isn't blank and isn't a zero, becomes last occupied cell
    If TheSht.Cells(iRo, 5).Value <> "" And TheSht.Cells(iRo, 5).Value <> 0 Then LastOcc = iRo
Next
End Sub

HTH, Dai

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: Deleting Rows based on not meeting several text criteria

Wed, 09 Mar 2011 04:00:52 -0700

Posted a 'reply on the VBA forum.

OK, from what I can gather, you just need to check column A to see if it's in the list of 30 companies yes? We'll go with your example and assume the companies are named A,B,C etc. The following code should work:

Const DJIAlist As String = "|A|B|C|D|E|F|G|H|I|J|K|L|M|N|O|P|Q|R|S|T|U|V" & _
"|W|X|Y|Z|AA|AB|AC|AD|" 'list of companies, with a pipe "|" character before and after their name

Sub RunRemoval()
Dim IsComplete As Boolean
IsComplete = RemoveNonDJIA(ActiveWorkbook.ActiveSheet) 'Run removal function on current sheet
If IsComplete = True Then 'if it ran OK...
    MsgBox "Everything looks kosher!"
Else
    MsgBox "It's all gone Pete Tong!"
End If
End Sub

Public Function RemoveNonDJIA(xSht As Worksheet) As Boolean
Dim iRow As Integer
On Error GoTo WeGottaGetOuttaThisPlace
iRow = 2
Do
    If InStr(1, DJIAlist, "|" & xSht.Cells(iRow, 1).Value & "|") > 0 Then 'if data in column A is in DJIA list...
        iRow = iRow + 1 'go to next row
    Else
        xSht.Rows(iRow).EntireRow.Delete 'delete entire row
    End If
Loop Until xSht.Cells(iRow, 1).Value = "" 'loop until blank cell found in column A
RemoveNonDJIA = True
Exit Function
WeGottaGetOuttaThisPlace:
RemoveNonDJIA = False
End Function

This will delete any lines on the current sheet that aren't in the list of DJIA. Beware, this code stops when it reaches a blank cell in column A, so if you have any rows that are blank in col A, it'll stop too soon, unless you change the code to only stop when columns A and C are blank, or whatever combo works for your data. If you wish to run it on all sheets at once, use the following subroutine instead of "RunRemoval":

Sub RunAllRemovals()
Dim IsComplete As Boolean, IsGood As Boolean, xSht As Worksheet
IsGood = True

For Each xSht In ActiveWorkbook.Sheets
    IsComplete = RemoveNonDJIA(xSht) 'Run removal function on each sheet
    If IsComplete = False Then IsGood = False
Next

If IsGood = True Then 'if all ran OK...
    MsgBox "Everything looks kosher!"
Else
    MsgBox "Something's gone Pete Tong!"
End If
End Sub

Hope this helps,
Dai

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: okay beginner here who needs help

Thu, 03 Mar 2011 09:10:47 -0700

Posted a 'reply on the MS-DOS forum.

You probably have a solution by now but for anybody who comes across this page in a search, the answer is quite simple:

del *1*.*
(In your example: del C:\1161_DataFiles\DOS\*1*.*)

will delete any files with a 1 in the name, to delete folders too, you would simply need:

del *1*

For more on DOS commands this site is pretty good:

http://www.computerhope.com/msdos.htm

HTH

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: Needs help on basics of windows programming.

Thu, 03 Mar 2011 09:02:25 -0700

Posted a 'reply on the Windows programming forum.

As with anything, there are multiple solutions so I'll suggest just one. I would think your best option is to use a .NET language, C# or VB, both similar, C# is slightly better but VB is slightly easier. So pick one and download the applicable language from the Visual Studio Express downloads page:

http://www.microsoft.com/express/Downloads/Download-2010.aspx

Once you have that, create a simple app with a FileSystemWatcher in it. This is essentially a function to watch a file and when a change occurs to it, it will do whatever you've programmed it to do, in this case take a copy of the file whenever it is changed. More informative info on the FileSystemWatcher class here:

http://www.eggheadcafe.com/tutorials/aspnet/fd3891ed-db38-4149-83a2-95a3a49868b5/how-to-use-filesystemwatc.aspx

Hope this helps, Dai

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: How to Change the Value in One Cell Based on Value in Another Cell

Sat, 14 Nov 2009 11:31:48 -0700

Posted a 'reply on the Beginner VB forum.

Hi, you had the right idea with =IF(F4 > 49,100,200) only you enter that directly into the cell, not use conditional formatting, that feature is for changing the cell's format, bold, underline, borders, colours etc. Dai

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: Writing an SQL Query

Mon, 09 Nov 2009 14:40:28 -0700

Posted a 'reply on the MySQL forum.

SELECT First Name, Surname, Sex, FROM NameOfTable WHERE Age > 25 AND Position = 'Technician'

Obviously change NameOfTable to the actual table name, if you wanna be fancy you could add at the end:

ORDER BY Age Asc

or

ORDER BY Age Desc

To sort it by age, ascending or descending, or you could sort it by Surname or whatever. Dai

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: Plz help me...

Mon, 19 Oct 2009 13:14:06 -0700

Posted a 'reply on the Beginner VB forum.

Don't log on often so I'm probably too late, but if you still need help then use something like this:

        Dim EndNum As Double 'The last number to add (user's input)
        Dim MyNum As Double 'Number to increase and add to total
        Dim TotalNum As Double 'Total sum

        On Error GoTo Chamone 'Exits if no number or a letter is typed
        EndNum = InputBox("Enter a postive integer value.", "Input Needed", "10") 'Gets user input number
        TotalNum = 0 'The sum, starts at 0
        MyNum = 1 'Start at 1
        Do 'Begin Loop
            TotalNum += MyNum 'Add the current number to the total
            MyNum += 1 'Increase the current number by one
        Loop Until MyNum > EndNum 'End loop when number to add is greater than input number
        MsgBox("The sum of the numbers 1 through " & EndNum & " is " & TotalNum.ToString())
        Exit Sub 'If successful, exits here
Chamone:  'Label used for when input is empty or invalid (letters typed etc.)
        MsgBox("Error, please type a valid number")
    End Sub

I suspect your problem was because you were using a string to get the input number, maybe not, doesn't matter, I know this code works. Dai.

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: start /wait does not wait for process to finish

Fri, 21 Aug 2009 11:51:05 -0700

Posted a 'reply on the MS-DOS forum.

I know that in XP (not sure about vista) DOS does not support the wait function on 32-bit win apps, only on 16-bit, so you'll have to find a workaround. My suggestion would be to create a loop to see if any new files are created after running MySQL and use that as the trigger, or if running MySQL uses up specific ports you could use the net send command to see once they have finished using them. Hopefully you can think of a better method. Hope this helps, Dai.

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: Calendar issues???

Wed, 19 Aug 2009 06:20:30 -0700

Posted a 'reply on the General programming forum.

Hi Kenny, I'm using XP SP2, I used Firefox(V3), Google Chrome(V2) and IE(V6), all rendered fine, when clicking on calendar dates nothing happened in Chrome or IE, is it supposed to? Firefox started transferring data but ultimately nothing happened. HTH, Dai.

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: VB text into textbox

Fri, 14 Aug 2009 06:51:16 -0700

Posted a 'reply on the Visual Basic forum.

Sorry, been a while since using classic VB, guess it doesn't support that method. OK, instead of:

text1.Text += a & " * " & b & " = " & a * b

Use:

text1.Text = text1.Text & a & " * " & b & " = " & a * b

It is essentially the same thing, I just normally do it the first way (I'm a .NET programmer if you hadn't guessed) as it's shorter and neater. Try that, should solve your problem, let me know if it doesn't. Dai.
------------------------------------------
Do or do not, there is no try. |
------------------------------------------

VB text into textbox

Thu, 13 Aug 2009 11:07:41 -0700

Posted a 'reply on the Visual Basic forum.

Hi Bob, you need to refer to the textbox itself, say it's called "TextBox1", then your code would be:

TextBox1.text = "My text here"

As you're using a loop I'm guessing you want to append the text each time, so you would need to change your line of:

Print a & " * " & b & " = " & a * b

To:

TextBox1.text += a & " * " & b & " = " & a * b

It is important to remember the plus sign before the equals sign, as otherwise you'll just overwrite it. Hope this helps, Dai.

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: DAI IT'S REALLY MAKES ME ANGER

Thu, 13 Aug 2009 10:53:55 -0700

Posted a 'reply on the C# forum.

Hi, sorry it took so long for a reply, had to wipe pc and re-install everything. To overcome this problem, you need to simply split the string in 2 and add the text in right place. Here's the modified bits of code:

First add these public variables:

    public partial class Form1 : Form
    {
        string str1;
        string str2;
        int CursPos;

Then change next part to:

        private void textBox1_KeyPress(object sender, KeyPressEventArgs e)
        {

            if (textBox1.SelectionStart < textBox1.Text.Length)
            {
                str1 = textBox1.Text.Substring(0, textBox1.SelectionStart);
                str2 = textBox1.Text.Substring(textBox1.SelectionStart);
                CursPos = textBox1.SelectionStart +1;
            }
            else
            {
                str1 = textBox1.Text;
                str2 = "";
            }

            if (e.KeyChar == 'a')
            {
                awrite();

            }

            // Rest of code as normal until public void uwrite

Then at public void uwrite:

        public void uwrite()
        {
            char ye;
            string word;
            ye = '\u06CC';
            word = ye.ToString();
            textBox1.Text = str1 + word + str2;
            // int Tlen = textBox1.Text.Length;
            // textBox1.Text = textBox1.Text.Substring(0, Tlen - 1) + word;
            if (str2 == "")
            {
                textBox1.SelectionStart = textBox1.Text.Length;
            }
            else
            {
                textBox1.SelectionStart = CursPos;
            }
        }

Apply above alteration to swrite, dwrite etc. You should find now that it works as intended, let me know either way. Dai.

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: suggestions for first moderately ambitious application?

Thu, 13 Aug 2009 06:26:51 -0700

Posted a 'reply on the C# forum.

Hi Bob. My suggestion would be to create a small game, that's what I do. Nothing too ambitious, something like build-a-lot or simcity is good, not too much moving (or detailed) graphics, mainly money calculations and doing game save/loads, gives quite a broad range of skills to practice. HTH, Dai.

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: List of Tables in VBA that hides deleted tables

Wed, 12 Aug 2009 13:57:17 -0700

Posted a 'reply on the Access forum.

Like I said it's rather difficult to solve it as I can't see the problem myself. The only thing I can think of trying is to run an sql query instead hoping that somehow it won't pick up the deleted databases. Try adding another query, just like the one you already made but this time change strSQL value to:

 strSQL = "SELECT Name FROM MSysObjects WHERE Type = 1"

Run that query and see if it has old table names or not (it will have the system tables, but we can filter them out later), if it works perhaps we can work from that to fill the list.
------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: Please to remove this error

Sat, 08 Aug 2009 17:08:00 -0700

Posted a 'reply on the HTML & WEB-Design forum.

Ah well, fair enough, glad to hear it. Yeah I'd put money on it being something clic.net had control over by the sound of it, you'll probably get a cheeky reply saying "what bug?" from them later.
------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: List of Tables in VBA that hides deleted tables

Sat, 08 Aug 2009 16:44:32 -0700

Posted a 'reply on the Access forum.

Hi breefc, try as I might I can't really re-create this problem, my databases never show deleted tables, so makes it hard to diagnose. However, it did sometimes display the tmp file of the table after deletion but this disappeared by the time I create a new table, I found it also deleted when I 'repaired' the database, so could you try seeing if that solves it. To do it:

Go to Tools > Database Utilities > Compact and Repair Database...

Really hope that works, if not send some more info if you can, preferably the database itself if possible (obviously empty all records first), or whatever code and screenshots you can. Dai.

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

C# Keypress

Sat, 08 Aug 2009 16:13:52 -0700

Posted a 'reply on the C# forum.

Hi, sorry for the late reply, you've probably solved it by now but anyway, I got the same problem as you and fixed it by adding:

 TextBox1.SelectionStart = TextBox1.Text.Length

after the textBox1.Text += word; line.
------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: Please to remove this error

Sat, 08 Aug 2009 15:07:44 -0700

Posted a 'reply on the HTML & WEB-Design forum.

Works fine in my browsers and I do not see any of the mentioned code, I hope this is a problem you've already resolved rather than a shameless plug for your website.

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Re: Creating list box OF tables within database

Tue, 04 Aug 2009 08:19:48 -0700

Posted a 'reply on the Access forum.

OK, what you need to first do, is add your listbox in design view, choose the 2nd option, which is something like "I will enter my own values", then hit finish so you have an empty box. Just as a double-check, right-click and properties the listbox and ensure the "Row Source Type" is set to "Value List", only way it works. Now you need to add this code, hit alt + F11 to get the VBE window open, find the code for your form (on left-hand side look for name of your form e.g. Form_CampaignsForm, it may be in a subfolder) and paste this:

Private Sub Form_Load()

Dim db As Database, tbl As TableDef
Set db = CurrentDb

For Each tbl In db.TableDefs
    If Left$(tbl.Name, 4) <> "MSys" Then
        List50.RowSource = tbl.Name & "; " & List50.RowSource
    End If
Next

End Sub

Simply change wherever it says List50 to the name of your ListBox!

To explain how it works, we use the Form_Load event so it does this once form is opened, you may assign it to a button or other control if you prefer, but I see no reason to.
Next 2 lines just set db as current database and tbl as a table.
The 'For Each' Loop then goes through each table in database and if the name of the table does NOT begin with MSys, then it adds the table name to the listbox.
The reason for not including MSys tables is that these are system tables and so usually not wanted, just take out the if statement if you do wish to include them.
Only problem I've found is that it puts each name above the previous one, so your list ends up being Z - A instead of A - Z, but that's easily altered, just put them into an array or something first.
Use exact same method for comboboxes too. Hope this helps, let me know if you need any further guidance, Dai.

------------------------------------------
Do or do not, there is no try. |
------------------------------------------