Posts by bnix
-
-
Re: Serial date conversion to semi string data.
It is probably overkill, but if you wanted to use a function with Insomniacs suggestion of CDate() it is simple enough to change my original one;
-
Re: Serial date conversion to semi string data.
You can try this
Code
Display MoreFunction ConvertTime(ByRef strValue As String) As String If strValue <> "" Then ConvertTime = Format$(strValue, "mmddyyyy_hhmmssAM/PM") End If End Function Sub Test() Dim str As String str = ConvertTime("38842.8287152778") Debug.Print str End Sub
you can pass in a cell value and it should do what you want
-
Re: Copying formulas
Welcome to the forum!
If you are wanting to fill down with the formula you can use;
There are other conditions that you can add but this is a simple example. Also, if your values are staying the same when you autofill, it sounds like you have your calculation set to manual and you would use SHIFT+F9 to calculate the current sheet.
-
Re: Dim'ing Userform ComboBoxes
Untested..but you can try
Code
Display MoreDim ComboX(10,4) As MsForms.ComboBox '............... Set ComboX(1, 1) = UserForm5.Controls("combo_name") set ComboX(1, 2) = UserForm5.Controls("next_combo_name") 'etc...... 'rest of code
You could also loop through the controls of the form and if it is a combobox then set the array at that time instead of having to hard code each Set line.
-
Re: Running Record Count
Still working on my first cup of coffee so I am not sure I am following exactly, but you could Filter the recordset as you mentioned and when you do so it does moves to the first record of the filtered recordset.
-
Re: Modify a resulting ADO.Recordset
Quote from iwrk4dedprI wonder!!
Can you run a "SQL" command agains an existing recordset. Seems like it should be possible. I've just never seen it done.
No, you cannot, at least not with ADO, you can Filter it, but you cannot query against an existing recordset.
-
Re: Modify a resulting ADO.Recordset
Look at the .GetRows() method, it returns an array from your recordset and allows you to pull in only the field you are wanting.
Code
Display MoreDim varArray Dim rs As ADODB.Recordset 'code to to set recordset varArray = rs.GetRows(,,<field_you_want>) 'loop through array for data
This might be useful for you if you are not wanting to loop through the records.
-
Re: Modify a resulting ADO.Recordset
If you are using ADO, you could do something along the lines of;
Code
Display MoreDim rsOne as New ADODB.Recordset Dim rsTwo as New ADODB.Recordset 'rsOne is where you are pulling the all 3 fields in original query 'create a new recordset and add only one field to it rsTwo.Fields.Append <fieldname>, <data_type> rsTwo.Open Do While Not (rsOne.EOF) rsTwo.AddNew 'update new recordset with only the field you are wanting to keep rsTwo.Fields(<fieldName>) = rsOne.Fields(<field_Name>) rsTwo.Update Loop 'no longer need rsOne rsOne.Close Set rsOne = Nothing
This is untested but this should meet what you need. Again, if you are using ADO, you might also look at the .GetRows() method.
-
-
Re: Copyright
More than likely, the company purchased the office license that your boss was using to develop the applications and his work was related to company information, so my thoughts are that this would probably fall under the whole intellectual property legalise category, of which I know nothing!
I have created many applications for my company and in the event I leave I simply take copies of my code and or applications and as Jack said leave the rest for them to figure out if any troubles arise. If problems do occurr and they need your bosses input, then he could charge them a consulting fee. Without a contract, I would not think your boss has a leg to stand on and the company can simply state that as an employee any thing he did is now company property.
-
Re: Importing data automatically
Try this.
Put this formula into C8 of the Preload workbook and copy down. You will need to change the path of the Master workbook.
=VLOOKUP(B8,'C:\<change to directory path on your machine>\[master.xls]Carrier #'!$A$7:$B$10,2,FALSE)
-
-
Re: Copy part of a row to a new row
Welcome to the forum.
Can you post the macro code of what you have so far and we can probably tweak that to do what you want.
-
Re: Can a VB app run VBA code within a workbook?
If I understand the question, are you saying you essentially want two identical projects that are running the same code, only you want one to run uninterrupted, while in the other you are stepping through the identical code?
Even in VB6, you would have to have two seperate IDE windows open in order to do what you are asking.
If you are wanting to do this, you could copy the workbook, rename it, then open both VBE windows in both workbooks, run the first one, then you can step through the other one. YOu would need two seperate XL windows open, do not open the books in the same application window though.
-
Re: Macro Behaves Differently on Second Run
I think Rich_z may have identified your problem.
From your description
Quote
If I quit Excel and relaunch it, it runs perfectly again even though code and source data have not changed in the first place.It sounds like you have some global or modular level variables that are not being cleared when entering the procedure again after the workbook is open.
-
Re: Skip if cell is emtpy or is not only a number
You could also try
Code
Display More'...start of code For i = 1 To UBound(vaData) If IsNumeric(vaData(i, 1)) And LenB(vaData(i, 1)) > 0 Then vaData(i, 1) = vaData(i, 1) * 1 End If Next i
You would probably have to adjust your looping structure so if the check skips a line you do not end up with a empty item in your array.
-
Re: formulas assistance required
Try
=SUMPRODUCT((Sheet1!$A$1:$A$100="MON")*(Sheet1!$B$1:$B$100="RAIN"))
-
Re: Large and Small
If there is some code behind the live feed you could do something there such as making the window automatically minimized or the application visible = false, but otherwise I am unaware of anything at this time to always keep the active window on top.
-