Posts by aysam

Hi all
I'm doing a formula using SUMIF
When the criteria in the formula is a date, it returns 0 Why ? I don't know
My formula is
Code:
=SUMIF(DEBTS!$A$5:$C$150;D2;DEBTS!$C$5:$C$150)
D2 is a cell that have a date
this formula results 0 value
even though it returns correct values if D2 is not a date
Hope I'm clear enough
using office Excel 2003
waiting

Re: Run a macro in three or more sheets at the same time

Thanks Roy for your follow and help
Greatly appreciated:thumbcoo:

Re: Run a macro in three or more sheets at the same time

Hi Roy
Thanks for the follow.
Here is my sample workbook.
I need the code to add rows work in the five worksheet at the same time using one button click.
the largest copy range is to be used.("A12:GG12")
Hope I'm clear enough.
Seems a problem in the forum attachment So here's a link
https://www.box.com/s/b3e6uhdgslladtkdq0ml

Re: Run a macro in three or more sheets at the same time

Thanks Roy for your help
I amended the array as required
but it still copies and pastes in only one sheet ;the active sheet

Re: Run a macro in three or more sheets at the same time

up

Re: Run a macro in three or more sheets at the same time

Quote from Barb-B;648045

Or, if you want to work only with some sheets in your workbook, and not others you could do something like this:

Code

For Each sht in Array(Sheets("Sheet1"), Sheets("Sheet3"), Sheets("fubar"))
        sht.Range("a1") = "Put something here"
    Next sht

Hi Barb_B
thanks for your try to help
But
it Pops out a run time error 92
for loop not initialized
and yellow highlights >>> next sht

Re: Run a macro in three or more sheets at the same time

Thanks for your help
I should try it and back

Hi all I have a piece of code
I want it to run in three sheets in my workbook at the same time
I searched the forum and tried a lot but I couldn't do it
Is there anyone could help ?
Of course there are many
My code is

Option Explicit


'******************************************************
'  ÊÚííä äØÇÞ ÇáÎáÇíÇ ÇáÊí íÊã äÓÎåÇ
Private Const MyRng_Copy As String = "A10:GG10"
'------------------------------------------------------
' MyRng_Copy ÊÚííä ÑÞã ÇáÚãæÏ ãä ÇáäØÇÞ
'   ÇáÐí ÓíÇÎÐ ãäå ÂÎÑ ÕÝ ááÕÞ
Private Const MyColumn As Integer = 1
'******************************************************




Sub Kh_Insert_Rows3()
On Error Resume Next
Dim MyRow As Integer, LastRow As Integer
MyRow = 1
MyRow = Application.InputBox(prompt:=" ÇÏÎá ÚÏÏ ÇáØáÇÈ ÇáãÊÞÏãíä ááÅãÊÍÇä " & Chr(10) & "ÚÏÏ ÇáØáÇÈ ÇáÇÝÊÑÇÖí " & MyRow, Title:="ÇÏÑÇÌ ÚÏÏ ãÍÏÏ ãä ÕÝæÝ ÇáÈíÇäÇÊ  ", Default:=MyRow, Type:=1)
If MyRow = False Then Exit Sub
With Range(MyRng_Copy)
    LastRow = Range(.Cells(1, MyColumn), .Cells(1, MyColumn).End(xlDown)).Rows.Count
    .Copy
    With .Offset(LastRow, 0).Resize(MyRow, .Columns.Count)
        .PasteSpecial xlPasteAll
        .SpecialCells(xlCellTypeConstants).ClearContents
    End With
    .Columns(3).Offset(LastRow, 0).Select
End With
Application.CutCopyMode = False
MsgBox "Êã ÅÏÑÇÌ ÇáÕÝæÝ ÇáãØáæÈÉ ÈäÌÇÍ", 524288 + 1048576, "ÇáÍãÏ ááå"
On Error GoTo 0
End Sub

I have got this code from here
The code is to password protect and unprotect a workbook in one go.
When I enter a wrong password it gives an error message
I want it to show a vba mesaage " Wrong Password " and go on.
Thanks in advance
the code is :

Private Sub CommandButton1_Click()
Dim wSheet As Worksheet
    For Each wSheet In Worksheets
        If wSheet.ProtectContents = True Then
           wSheet.Unprotect Password:=TextBox1.Text
        Else
            wSheet.Protect Password:=TextBox1.Text
        End If
    Next wSheet
Unload Me
End Sub

Peace to you all
I need a formula to execlude FRIDAYS from a serious of dates
I mean
to put a start date and copy it down without the Fridays
1/7/2010------------Thursday
3/7/2010------------Saturday
4/7/2010------------Sunday
5/7/2010------------Monday
6/7/2010------------Tuesday
7/7/2010------------Wednesday
8/7/2010------------Thursday
10/7/2010-----------Saturday
Thanks in advance

Re: .Find with MatchCase True and LookAt Whole Argument

Thanks so much
That was great

Peace to you all
I have a code that looks for a number in a column
The numbers in the column are not in the correct order
332
256
147
33
158
70

Private Sub CommandButton1_Click()
On Error GoTo 100
Dim d
Dim ra As Range
[COLOR=red]Columns(2).Find(C, MatchCase:=True).Activate[/COLOR]
A = MsgBox("Do you want to delete the data", 4)
If A = 6 Then ActiveCell.EntireRow.Delete
Exit Sub
100 MsgBox ("Error")
End Sub

the problem is
if I want to look for number ( 33 ) the code activates (332 )the first number that has 33
Any help 'd be greatly appreciated

Re: Use animated gif image in a user form

Thanks Sir AAE for your advice.
I'll ll go there but if I needed help , SURE I'll be back.
See you then.

Peace to you all
Is there a way to insert a gif animated picture on a user form and keep it moving then ?
I hope it could be done
Thanks in advance

Re: VBA macro simple adjust

Hi PCI
I tried your code but it selects the first Cell after the inserted rows
But it helped me greatly to acheive my goal which is
to select the first cell of the inserted rows
I added this piece to yours

ActiveCell.Offset(vRows - vRows + 1, 0).Select

OR

ActiveCell.Offset(1, 0).Select

Once again thanks soooooo much

Peace to you all
I have a macro to insert blank rows after the active cell . ("B6")
for example
It's OK
What I need is
to make the active cell after the insert to be
the first cell after the first active cell ("B7")
my macro is

Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0)
   ' row selection based on active cell
   Dim x As Long
   Application.EnableEvents = False
   If vRows = 0 Then
    vRows = Application.InputBox(Prompt:= _
      "How many rows do you want to add?", Title:="Add Rows", _
      Default:=1, Type:=1) 'Default for 1 row, type 1 is number
    If vRows = False Then Exit Sub
   End If
   Dim sht As Worksheet, shts() As String, i As Integer
   ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
       Windows(1).SelectedSheets.Count)
   i = 0
   For Each sht In _
       Application.ActiveWorkbook.Windows(1).SelectedSheets
    Sheets(sht.Name).Select
    i = i + 1
    shts(i) = sht.Name
    x = Sheets(sht.Name).UsedRange.Rows.Count 'lastcell fixup
    Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
     Resize(rowsize:=vRows).Insert Shift:=xlDown
    'Selection.AutoFill Selection.Resize( _
     'rowsize:=vRows + 1), xlFillDefault
    On Error Resume Next    'to handle no constants in range
     'to remove the non-formulas
    Selection.Offset(1).Resize(vRows).EntireRow. _
     SpecialCells(xlConstants).ClearContents
   Next sht
   Worksheets(shts).Select
   Application.CalculateFull
   Application.EnableEvents = True
End Sub

Peace to you all
I already have a code to printpreview multiple ranges

Private Sub CommandButton1_Click()
Sh = "Sheet1"
 A = Sheets(Sh).Range("J6")
 B = Sheets(Sh).Range("K6")
 C = Sheets(Sh).Range("M6")
 D = Sheets(Sh).Range("N6")
' to always put the first row in the printpreview
  Sheets(Sh).PageSetup.PrintTitleRows = Sheets(Sh).Rows(A).Address
' to always put the first column in the printpreview
  Sheets(Sh).PageSetup.PrintTitleColumns = Sheets(Sh).Columns(B).Address
 
' to put multiple columns in the printpreview
  Sheets(Sh).Range(C).PrintPreview
  Sheets(Sh).Range(D).PrintPreview
End Sub

It printpreviews only the first Range ( C )
I need it to print previews Ranges ( C ) and ( D ) or more in one page
I hope I'm clear enough
Any help'd be highly appreciated