Re: Sequential Numbering
Hi,
I found some code and modified it.
You do not need another worksheet for the Numbers.
The code I found and modified will create a ".txt" file and start the numbering seq. with "00001". Each time you open the Template the Seq. Nr will go to the next nr.
I hope this works for you.
Re: Sequential Numbering
Hi,
Probably the way to go is to add a worksheet for the "Quote Nr" and have it hidden.
Then you can write a code in the "This Workbook" module to look at the Quote sheet and get the last Quote Nr. then add 1 to it. Then you can have the Nr. populated to "B7" of the sheet you want.
All you wold have to do for the first number is populat it in the "Quote" work sheet.
I'll try o put some code for you.
Others on this forum may have a better suggestion.[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Hi,
This is what I have.
I did not hide the Quote worksheet.
Hope this helps
Re: Sequential Numbering
Hi,
I could not open your template. Could you repost your Attachment.
I'm using Excel 2003.
If it's 2007 then someone else may be of help.
Re: Combine Multiple Records
Hi,
The following code may help.
Paste it to a module.
I used sheet1 and column A as reference for the code you may have to correct. Also I didn't
Add the Headers for the Accts
Sub test()
Application.ScreenUpdating = False
Dim lrow As Long
Dim i As Integer, cnt As Integer
lrow = Sheets("Sheet1").Range("A65536").End(xlUp).Row
cnt = 1
For i = 2 To lrow
If Cells(i + 1, 1).Text = Cells(i, 1).Text Then
If Cells(i + 1, 1).Text = "" Then Exit Sub
Cells(i, 2 + cnt).Value = Cells(i + 1, 2).Text
cnt = cnt + 1
Cells(i + 1, 1).EntireRow.Delete
i = i - 1
ElseIf Cells(i + 1, 1).Text <> Cells(i, 1).Text Then
cnt = 1
End If
Next i
End SubRe: Which Search Function To Search Multiple Columns
Hi,
If you can post a example of your workbook.
Someone may be able to help.
Re: Autopopulate A Textbox Based On The Entry Of Another Textbox
Hi,
Made changes to code.
What this code does when you tab off of the textbox for the cust. code it should go to the sheet that has the cust codes along with the customer name. You will need to change sheet name and listbox name where necessary.
Paste the code to userform code module.
I do not think you can do a Vlookup in this case.
Private Sub YourkeyName_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) 'tab key
Application.ScreenUpdating = False
If KeyCode = "9" Then
Worksheets("sheet1").Activate '''change to sheet name that contains the customer ''code
With Userform1''change to your userform name
custcode = .Textbox.Text ''should be the textbox that has the cust code
Cells.Find(What:=custcode).Activate
.Textbox.value = ActiveCell.Offset(0, 1).Text ''this is the cell/row cust id is and ''populates the textbox
End With
End If
End SubRe: Autopopulate A Textbox Based On The Entry Of Another Textbox
Hi,
Here's some code that may help.
When the user tabs off the textbox "tbStore" in the example it searches for the store name and then populates a lable, but in you case it can be another textbox.
Private Sub tbstore_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) 'tab key
Application.ScreenUpdating = False
If KeyCode = "9" Then
Worksheets("sheet1").Activate
With msgcenter
store = .tbstore.Text
Cells.Find(What:=store).Activate
.Label19 = ActiveCell.Offset(0, 1).Text & " " & ActiveCell.Offset(0, 2).Text
End With
End If
End SubRe: Clear Multipal List Boxes When Changed
Hi,
The following code should get you started.
Case "Meal Deal 1"
ListBox2.RowSource = "Sheet1!G2:G56"
ListBox3.RowSource = ""
ListBox4.RowSource = ""
ListBox5.RowSource = ""
ListBox6.RowSource = ""
ListBox7.RowSource = ""Re: List Values Meeting Date Criteria
milyenabox,
I modified zimitry code to what I believe you wanted.
Sub Opensorter()
Dim xlShtBalance As Worksheet
Dim xlShtx As Worksheet
Dim xlLngRow1 As Long
Dim xlLngRow2 As Long
Dim xlLngRow3 As Long
Dim xlInt As Integer
Dim xlDate As Date
Set xlShtBalance = Sheets("balance")
Set xlShtx = Sheets("x")
xlLngRow1 = xlShtBalance.Cells(Rows.Count, 1).End(xlUp).Row
xlLngRow2 = xlShtBalance.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
For xlInt = 1 To xlLngRow1 Step 1
If IsDate(xlShtBalance.Cells(xlInt, 1)) Then
xlDate = DateValue(CDate(xlShtBalance.Cells(xlInt, 1)))
xlLngRow3 = xlShtx.Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To xlLngRow3 Step 1
If IsDate(xlShtx.Cells(i, 1)) Then
If xlDate <= CDate(xlShtx.Cells(i, 6)) And CDate(xlShtx.Cells(i, 1)) <= xlDate And CDate(xlShtx.Cells(i, 6)) <> xlDate Then
xlShtx.Range("A" & i, "F" & i).Copy Destination:=xlShtBalance.Cells(xlLngRow2, 4)
xlShtBalance.Cells(xlLngRow2, 3).Value = Format(xlDate, "MMM-DD-YYYY")
End If
End If
xlLngRow2 = xlShtBalance.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
Next i
End If
xlLngRow2 = xlShtBalance.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
Next xlInt
Set xlShtBalance = Nothing: Set xlShtx = Nothing
End SubRe: Loop If And Through List Of Trades
milyenabox,
If you use the dates in column "A" (balance sheet) and then compare that with the open and close date in your worksheet(X) this would create a long list of trades.
Re: The Way Around Text Appears
Hi,
Select the cell or cell range you want to change, then Right Click, select Format Cells, select Alignment, then click the word "Text" in the otientation box.
Re: Populate Listbox With Hidden Sheet Names
Thanks for letting me know.
Re: Populate Listbox With Hidden Sheet Names
Hi,
See if this works for you.
Private Sub UserForm_Initialize()
Dim wsSheet As Worksheet
Dim lngIndex As Long
With ThisWorkbook
ReDim strarray(.Worksheets.Count - 1, 1) As String
lngIndex = 0
For Each wsSheet In .Worksheets
strarray(lngIndex, 0) = wsSheet.Name
If wsSheet.Visible = xlSheetHidden Then
lngIndex = lngIndex + 1
End If
Next
End With
With ListBox1
.List = strarray
End With
End SubRe: Copy Rows In New Sheet Based On Date Range Criteria
ahmedwaseem2000,
Attached is a workbook that may help. It will produce a userform when the "ClickMe" is clicked. You can then enter dates with the format shown on sheet1.
Re: Checking For Expired Dates In A Range
stickyfeet,
Try this. If a empty cell is encountered it will not product the userform.
Private Sub Workbook_Open()
'Check Expiry Dates
Dim ACell As Range
For Each ACell In Sheets("Sheet1").Range("i7:i100")
If ACell.Value = Date And ACell.Value <> "" Then
UserForm5.Show
GoTo AA
ElseIf ACell.Value < Date And ACell.Value <> "" Then
UserForm5.Show
GoTo AA
Else
End If
Next ACell
AA:
End SubRe: Close Worksheet
hpoh,
This will close the workbook not a worksheet which I assumed you want.
Sub CloseMe()
Application.DisplayAlerts = False
Workbooks.Close
Application.DisplayAlerts = True
End SubRe: Removing Non Alphanumeric Characters From Text
Hi,
Could you supply a example of your worksheet. This way we'll have a better ideal of what you want.
sorry I see batman posted a code.
Re: Writing Data To Open Workbook
nmutimer,
If your using a userform this may help.
Private Sub Commandbutton1_Click()
Application.ScreenUpdating = False
Dim lrow As Long
'''' Change to your sheet Name ''''
lrow = Sheets("Sheet1").Range("A65336").End(xlUp).Row + 1
With UserForm1
Cells(lrow, 1).Value = .TextBox1.Text
Cells(lrow, 2).Value = .TextBox2.Text
Cells(lrow, 3).Value = .TextBox3.Text
Cells(lrow, 4).Value = .TextBox4.Text
'''''' This will reset the textbox to "" for next entry'''
.TextBox1.Value = ""
.TextBox2.Value = ""
.TextBox3.Value = ""
.TextBox4.Value = ""
End With
End SubRe: Endless Loop Within A Loop
face2face,
Not really sure, but I tried you code as is and I do not see where in your loop
"Loop While Cells(22, c) > Cells(20, c)"
Cells(20,c) is ever greater than Cells(22,c)
I ran a debug and Cells(22,c) hade a value of "9"
At some time it has to be greater other wise it loop's.
Re: Endless Loop Within A Loop
face2face,
If the loop for "d" is satisifed then you could put a "Exit For" which will take you out of the loop.
Also check "sumrng = 0" it appears that it is being set to "0" thus stopping your
"Loop While Cells(22, c) > Cells(20, c)" Cells(22,c) appears to always greater than
Cells(20,c).
For d = 5 To 253 Step 1 'to find the period on the row 1
If Cells(20, c) < Cells(2, d - 1) And Cells(20, c) > Cells(2, d) Then
Cells(21, c) = Cells(1, d) - 1
Exit For
End If
Next d