Posts by anka1829

anka1829 · Sep 27th 2007

Re: Division Decimal Format

Try with this code:

Code

Sub Macro()
   Dim divider, divisor, result As [COLOR="Blue"]Double[/COLOR]
 
   divider = 754
   divisor = 333
   result = divider / divisor
   
   MsgBox CDec([COLOR="blue"]Format[/COLOR](result, "0000.00"))


End Sub

Display More

Regards,
Antonio

anka1829 · Apr 17th 2007

Re: Autofilter Top 10 Ignoring Other Filter Options

You can choose menu data, then filter then show all each time before to select your new filter.

Regards,
Antonio

anka1829 · Apr 6th 2007

Re: Calling A Routine In Another Workbook Whose Name Contains An Apostrophe

Try so:

Code

Application.Run Workbooks(objWorkbook).FormatData

Regards,
Antonio

anka1829 · Apr 6th 2007

Re: Continue Finding

Try with this code (may be it's possible to do shorter code) that select the cell with a row number greater than current position (activecell):

Code

Sub Find_cust()
   Dim iRow As Long
   Dim icurrentRow As Long
   Dim lastRow As Long
   Dim swEnd As Boolean
   icurrentRow = ActiveCell.row
   
   lastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
   
   If Range("N3").Value = "" Then
      MsgBox "Please enter a customer name"
      Exit Sub
   End If
   valueToLookFor = Range("N3").Value
   
   With Worksheets("CD").Range("b:b")
      Set found = .Find(valueToLookFor, LookIn:=xlValues)
      If Not found Is Nothing Then
         iRow = found.row
         firstAddress = found.Address
      
         swEnd = False
         Do Until (Not found Is Nothing _
               And iRow > icurrentRow) _
            Or (found.Address = firstAddress _
               And swEnd) _
            Or found Is Nothing
            DoEvents
            
            swEnd = True
            Set found = .FindNext(found)
            If Not found Is Nothing Then
               iRow = found.row
            End If
         Loop
      End If
   End With
   
   If iRow > 0 Then
      Cells(iRow, "A").Select
   End If
End Sub

Display More

Regards,
Antonio

anka1829 · Apr 6th 2007

Re: Select Cell With Value I Type

To try to use on another workbook/worksheet you can try to right click on your sheet label, choose code view and past this code:

Code

Private Sub Worksheet_Change(ByVal Target As Range)
  'here macro see if you are changing value in range 'a1' (where you may disgit your data to look for)
   If Target.Address(False, False) = "A1" Then


     'here read the value of the range 'a1'
      valueToLookFor = Range("a1").Value
 
     'here I search the the value
      Set found = ActiveSheet.Range("a3:a50000").Find(valueToLookFor, LookIn:=xlValues)


      'if the found the value goes at column 'b' of the row where it founds the data
      If Not found Is Nothing Then
         iRow = found.Row
         Cells(iRow, "b").Select
      End If
   End If
End Sub

Display More

Regards,
Antonio

anka1829 · Apr 6th 2007

Re: Is Vlookup The Correct Method For Me?

I used the match formula that look for data in a range and returns the row (not the absolute row but the row number in the range) where it found the information.

For example, if you have these data in b1:b3 :
"Example1"
"Example2"
"Example3"

and write:

=match("Example2";B1:B3;0) it will return '2' that is the row where it found the string "Example2"

At this point to get other information on the same row I used the Index formula (range where to get data, row, column), for example:
=INDEX(A1:E3;2;3) will return the information from cell 'C3'

and replacing the row with the match formula it will return information from cell in column '3' or 'C' of the row where column 'B' has the information you were looking for.

Regards,
Antonio

anka1829 · Apr 6th 2007

Re: Select Cell With Value I Type

See the attached file... I hope it can help you.

Regards,
Antonio

anka1829 · Apr 6th 2007

Re: Is Vlookup The Correct Method For Me?

See the attached file... I hope it can help.

Regards,
Antonio

anka1829 · Apr 6th 2007

Re: Convert Date To Year/week Of Year/day Of Week

I'm sorry... I didn't see the answer of Wigi

anka1829 · Apr 6th 2007

Re: Convert Date To Year/week Of Year/day Of Week

Try with:

=YEAR(A1)-2000&RIGHT("00"&WEEKNUM(A1);2)&WEEKDAY(A1)

Regards,
Antonio

anka1829 · Mar 26th 2007

Re: Running Code Based On Option Selected In A Combobox

Try with this code:

Code

Select Case ComboBox1.ListIndex + 1
      Case 1
         Call mySub_1
      Case 2
         Call mySub_2
      Case 3
         Call mySub_3
   End Select

Regards,
Antonio

anka1829 · Mar 26th 2007

Re: Macro - Jump To Today On Worksheet

See the attached file.. I hope it is what you need.

When you post some code, select all the code and click on the '#' icon.
So the code will be put in code tags.

Regards,
Antonio

anka1829 · Mar 16th 2007

Re: Only Allow Workbook To Close If Data In All Cells

No.

You have to add the code at the workbook close event and so (only for this code) you have to do so:
1) open excel
2) open excel vb editor (usually keyboard 'Alt' and 'F11')
3) on the left frame give double click on 'ThisWorkbook' (if you don't see the frame use keyboard shortcut 'Ctrl' and 'R')
4) on the upper of the right windo there are two combo box (general and declaration)
5) from the combo box on the left (general) choose Workbook
6) from the right combo box choose 'BeforeClose' event
7) here paste the code

Regards,
Antonio

anka1829 · Mar 16th 2007

Re: Only Allow Workbook To Close If Data In All Cells

I apologize for the bad color I used

Code

Option Explicit 
 
Private Sub Workbook_BeforeClose(Cancel As Boolean) 
    Dim rCheck As Range 
    Dim rCl    As Range 
    Cancel = True 
    Set rCheck = Range("C2:C10") 
    For Each rCl In rCheck 
        If rCl.Value = "" Then 
            MsgBox "Data Incomplete!", vbCritical, "Error" 
            [COLOR="Blue"][B]Cancel = true [/B][/COLOR]
            Exit Sub 
        End If 
    Next rCl 
     [COLOR="blue"][B]'ThisWorkbook.Close[/B][/COLOR]
End Sub

Display More

anka1829 · Mar 16th 2007

Re: Only Allow Workbook To Close If Data In All Cells

I think it's better to modify the royUK code so:

Code

Option Explicit 
 
Private Sub Workbook_BeforeClose(Cancel As Boolean) 
    Dim rCheck As Range 
    Dim rCl    As Range 
    Cancel = True 
    Set rCheck = Range("C2:C10") 
    For Each rCl In rCheck 
        If rCl.Value = "" Then 
            MsgBox "Data Incomplete!", vbCritical, "Error" 
            [COLOR="PaleGreen"]Cancel = true[/COLOR]
            Exit Sub 
        End If 
    Next rCl 
    [COLOR="palegreen"]'ThisWorkbook.Close[/COLOR] 
End Sub

Display More

Because I think if you don't use the Cancel = true excel will close the workbook

Regards,
Antonio

anka1829 · Mar 16th 2007

Re: Convert Basic To Vba

I'm sorry... I forgotten top put attached file

anka1829 · Mar 16th 2007

Re: Convert Basic To Vba

See the attached file

I only changed your code from Sub to Function and at the end of the function added one row of code

Then I put the function in a module and I call it from the excel as a function.

I hope it can help you.

Regards,
Antonio

anka1829 · Mar 15th 2007

Re: Get Data From Last Filled Cell In A Column

Try with this code:

Code

Sub Macro1()
   colWhereGetData = "c"
   lastCellData = Cells(Rows.Count, colWhereGetData).End(xlUp)
   
   ActiveCell.Value = lastCellData
End Sub

Regards,
Antonio

anka1829 · Mar 15th 2007

Re: Delete Cells Shift Left

Try with this code... it may fix your problem.

Code

Sub Macro1()
   Dim rng As Range, i As Long
   Dim iTotalRows As Integer
   Dim lastColumn As Long
   
   iTotalRows = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   For r = 1 To iTotalRows
      lastColumn = Cells(r, Columns.Count).End(xlToLeft).Column
      'the perfect number is 23
      If lastColumn > 23 Then
         For c = 1 To 3
            Cells(r, 20 + c) = Cells(r, lastColumn + c - 3)
         Next
      ElseIf lastColumn = 22 Then
         For c = 3 To 1 Step -1
            Cells(r, 20 + c) = Cells(r, lastColumn + c - 3)
         Next
      End If
   
   Next
   Columns("x:ag").Delete
End Sub

Display More

I hope this is what you need

Regards,
Antonio

anka1829 · Mar 6th 2007

Re: Do Until And Crash

DoEvents allows at operating system to execute other events.

In other words, if you have a do until.... loop and you don't use DoEvents you may be serious problems to stop the execution while you have no problems if you use DoEvents because the system stop for a while the execution and then allows you to stop the execution.

Use DoEvents also after an insert so excel can have a while to update.

Another thing: in your Sub... End Sub try to define your variables so when you exit from the Sub the variable stop to exist:
Private Sub Macro1()
dim myVar1 as string

End Sub

when you exit from the Sub the variable doesn't exist.

I hope it can help you

Regards,
Antonio