|
Hello, I am new to VBA (self taught) and I'm looking for a shortcut on writing an IF STATEMENT. I have an Excel spreadsheet that contains 3,070 rows of Bin Numbers. The report is similar to the below columns.
510250000 510250999
510251000 510251999
510252000 510252999
510253000 510253999
Soon I will receive a report on a daily basis, which will need to be bumped next to these bin numbers looking for a match.
I wrote the following IF STATEMENT
If strbin >= 510250000 and <= 510250999 _
or strbin >= 510251000 and <= 510251999 _
or strbin >= 510252000 and <= 510252999 _ then
Cells(irow, 1).Interior.ColorIndex = 34
As mentioned above, by the time I've completed the above statement I will have over 3,000 rows.
I would appreciate any feedback on a shortcut.
Thank you in advance for your help.
Fountain
|
|
|
|
|
Uhhh...Your IF statement doesn't have to be multiple ranges like that, because, so far, your range is 510250000 to 510252999, with no breaks between the ranges you listed!
What's the logic behind what you want to do? So far, all we've seen is that your coloring cells, but why? What determines what color the cell is going to be? And what is the problem that your having? Is it performance related?
RageInTheMachine9532
|
|
|
|
|
Hello fellow VB members :
I am student in college and learing VB , i have an old notebook that has only W98 and VB.
I have been reading about vb.net and wanted to seek your advise on how i can get access to VB.NET and check some of the codes that i have written for interactive forms tested out ....
like renting some space with a Internet provider and transfer these files across and check out....
|
|
|
|
|
Hai I am very new to VB.NET.I am retrieving the records from the Access Database and displaying it in a DataGrid using Data Form Wizard.I am retrieving only selected fields record.When I click on any of the row , I should get all its related field values in the text boxes which I designed mannually.I am confused whether to display the records in the data grid in this way or there is any other way.Also can any one please let me know how to get all the field values?
Thanks
Anju
|
|
|
|
|
Hello,
I have 2 very small vbs scripts that are fully tested and ready to go. Can someone convert them to an exe using vb.net for me please?
Joe
|
|
|
|
|
Well, there is no compiler for turning the script into an EXE. But you can encrypt the script using the Script Encoder Tool from Microsoft, found here[^].
RageInTheMachine9532
|
|
|
|
|
hello,
I gotta a question
if I insert an image to a rich text box
is there anyway to stop the ability to resize it?
|
|
|
|
|
Only if you set the Enabled property of the RichTextBox to False.
RageInTheMachine9532
|
|
|
|
|
Hi Guys,
I've got a problem while using APIs for printing text .
The problem: Every consecutive newline of text needs to
be printed with different fontsize and also some lines need
to printed in bold.
Well I have been using 'WritePrinter API'. (its one the APIs required for printing with WIN32 APIs);
Are there any APIs that manipulate the printers fontsize,fontbold ,etc output.
API wizards kindly help...
-SimSan
|
|
|
|
|
In between lines try
Printer.Font.** = ??
Printer.Font.** = **
or
Printer.FontBold = true
Printer.FontSize = 12
Note: even though members don't show up with Intellisense when you hit the period doesn't mean that they aren't there
hope this helps
If it's broken, I probably did it
bdiamond
|
|
|
|
|
Well I am printing a page directly
to the printer bypassing the driver.
WritePrinter is one of the APIs used
just to do that job.My collegue calls it
DOS printing; if that sounds familiar.
Printing with Printer object wouldnt
give a draft mode print....would it????
I came across SelectObject API ,but still
I dont get the required output.
-SimSan
|
|
|
|
|
say suppose i have developed a application by vb and when i will run my application the a new item will be added on the tool bar of ms-word and as well as a new menu
item will be added in any of menu of the ms-word and when some one will choose my item from menu or click on tool bar button the notepad.exe will run.
i could i do this.
tbhattacharjee
|
|
|
|
|
how would i go about retrieving input from a multiline text box?
the task i want to do is to get the data on each line of the box, line by line. i want each line entry to be saved into other labels, or variables, or anything.
i thought of using a for/next loop to check each individual chr of the box until a ascii(10) or ascii(13) chr is reached. these are, i think, the CR and LF characters which determine a new line and end of line. theres gotta b a better way?
Thank you
------------------------
Jordan.
III
|
|
|
|
|
If your using a single multiline textbox to get input for multiple fields, you really should reconsider your input method. Using a single textbox makes input validation a bit of a chore.
On the other hand, why not use the Lines() property of the TextBox? It returns an Array of Strings, one array item per line.
RageInTheMachine9532
|
|
|
|
|
hmm.. i nvr used that line() property before, ill chk it out, thanks.
however, since the site was down last nite, i had nothing to do but test my elaborate theory. i got it to work. i had to save each line from the txtbox (up to 4 lines) to 4 different textboxes (singlelined). here is how i did it:
For i = 1 To textLarge.TextLength<br />
If Mid(textLarge.Text, i, 1) = Chr(13) Then<br />
labelNum += 1<br />
i += 1<br />
Else<br />
If labelNum = 1 Then<br />
text1.Text &= Mid(textLarge.Text, i, 1)<br />
End If<br />
If labelNum = 2 Then<br />
text2.Text &= Mid(textLarge.Text, i, 1)<br />
End If<br />
If labelNum = 3 Then<br />
text3.Text &= Mid(textLarge.Text, i, 1)<br />
End If<br />
If labelNum = 4 Then<br />
text4.Text &= Mid(textLarge.Text, i, 1)<br />
End If<br />
End If<br />
Next
i could have made it more efficient by using an array of txtboxes instead of 4, so i could reuse the code, but, hey...
------------------------
Jordan.
III
|
|
|
|
|
hi,
I want to change font type in Menu to a different language font in VB.Net. Can any one help me.
thanks
|
|
|
|
|
Currently i am using the below coding to create a text file and write something on it..
Dim wfile As New System.IO.StreamWriter("c:\test.txt")<br />
wfile.WriteLine("Hello" & vbcrlf & "Are" & vbcrlf & "You")<br />
wfile.Close()
The text file content should be like this
Hello
Are
You
What if i want to insert "How" into the text file to modify the text file become like this..
Hello
How
Are
You
Anyone can provide the coding on how to get the specific line in a text file and insert lines of text into the text file?Any help is greatly appreciated... Thanks..
|
|
|
|
|
it can be done. i dont know how, tho..
i do know how to append text though.. that is, to add text to the end of a text file. i dont know how to go about inserting text onto the specific line/location into an existing file, sry. however if the appending text method is suitable, i can explain it.
------------------------
Jordan.
III
|
|
|
|
|
thanks for replying me.... can u tell me ur method how to append text as well? thanks for help..
|
|
|
|
|
Open the file for Append and start writing.
Dim myFile As New FileStream( "filename.txt", FileMode.Append )
myFile.WriteLine( "Text I want to append..." )
myFile.Close()
RageInTheMachine9532
|
|
|
|
|
You can't "insert" text without rewriting the file from the point where you are inserting. There are a couple of ways to do it:
1.) Seek to the point where you want to insert text, read the text from that point to the end of the file and store it. Seek back to the point where you want to insert text and write what you wanted to insert, then write everything you read in and stored previously. Done...
2.) Create a second file (text2.txt). Read everything from your original file (text1.txt), up to the point where you want to insert, and write it out to text2.txt. Write out what you want to insert. Then finished reading text1.txt and write everything out to text2.txt. Then delete text1.txt and rename text2.txt to text1.txt. Done...
RageInTheMachine9532
|
|
|
|
|
I want to add a section in my vb.net application to ask the client if they want to format a diskette or not. If they answer yes, go ahead and format a diskette in drive a:.
Does anyone have some code in Vb that will format a diskette?
Any Help is Appreciated
|
|
|
|
|
You could try using the Windows API SHFormatDrive() function -- here's a class that wraps it:
Public Class ShellUtilities
Public Declare Unicode Function SHFormatDrive _
Lib "Shell32.dll" _
(ByVal hwnd As IntPtr, _
ByVal iDrive As Int32, _
ByVal iFormatID As Int32, _
ByVal iOptions As Int32) As Int64
Public Const SHFD_CAPACITY_DEFAULT = 0 'default drive capacity
Public Const SHFMT_OPT_FULL = 1
Public Const SHFMT_OPT_SYSONLY = 2
Public Shared Sub FormatDrive(ByVal dialogOwner As IntPtr, ByVal iDrive As Int32)
SHFormatDrive(dialogOwner, iDrive, SHFD_CAPACITY_DEFAULT, SHFMT_OPT_FULL)
End Sub
End Class
And here's an example of calling it from a windows form button click:
Const A_Drive = 0
Const B_Drive = 1
Const C_Drive = 2
' ... etc ...
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
ShellUtilities.FormatDrive(Me.Handle, A_Drive)
End Sub
|
|
|
|
|
Try this:
Const SHFMT_ID_DEFAULT = -1
Const SHFMT_OPT_FULL = 1
Const SHFMT_OPT_SYSONLY = 2
Const SHFMT_ERROR = -1 ' Error on last format, drive may be formatable
Const SHFMT_CANCEL = -2 ' Last format was canceled
Const SHFMT_NOFORMAT = -3 ' Drive is not formatable
' SHFormatDrive takes 4 parameters:
' hWnd The handle to the window that will be the dialogs parent window.
' Drive The drive number to format, 0 is A:, 1 is B:, 2 is C:, ...
' fmtID Must always be SHFMT_ID_DEFAULT, or the value ????.
' options Opitons.
Private Declare Function SHFormatDrive Lib "shell32" ( _
ByVal hWnd As IntPtr, _
ByVal Drive As Integer, _
ByVal fmtID As Integer, _
ByVal options As Integer) _
As Integer
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim iDrive As Integer
Dim rc As Integer
iDrive = 0
rc = SHFormatDrive(Me.Handle, iDrive, 0, 0)
Select Case rc
Case SHFMT_ERROR
MsgBox("Format failed, user pressed CLOSE")
Case SHFMT_CANCEL
MsgBox("Format cancelled - user hit CLOSE")
Case SHFMT_NOFORMAT
MsgBox("Unable to format this disk drive")
Case Else
MsgBox("Disk was formatted successfully and returned: " & rc)
End Select
End Sub
This will show the format dialog that you see when you do a format from the Explorer Shell. The format won't start until the user presses OK.
RageInTheMachine9532
|
|
|
|
|
Thank you very much for your feedback. I used the code that everyone submitted and it worked great!!
Is there a way to pass the Label that you want to use when formatting the diskette.
I found the SHFormatDrive documentation on MSDN, but I really don't see a way to pass a Label in.
Thanks again for the help,
Kevin
Lost in the vast sea of .NET
|
|
|
|