Onkey Or Keydown Withing Worksheet?

 
Worksheets("Sheet1").Activate
  'Can't select unless the sheet is active

If ActiveCell.Value <> Selection.Offset(1, 0) Then

ActiveCell.Borders.Item(xlEdgeBottom).Weight = xlMedium

Selection.Offset(1, 0).Select

Else: Selection.Offset(1, 0).Select
End If

Sub Tidy_Worksheet()
'
' Tidy_Worksheet Macro
' Macro recorded 04/02/2007 by Alex
'
' Keyboard Shortcut: Ctrl+Shift+T
'

Application.ScreenUpdating = False

Workbooks("Unit13.xls").Worksheets("Bulk").Range("A3").Select

'Clears the row containing no data
Do While IsEmpty(ActiveCell) = False
 numwords = Application.CountIf(Sheets("Bulk").Range("A3", Range("A3").End(xlDown)), ActiveCell)
  If numwords > 3 And Selection.Offset(0, 1) = "" Then
   Selection.EntireRow.Delete
  Else: Selection.Offset(1, 0).Select
  End If
Loop


'Sorts the data into ascending order moving the SKU number to the top of their locations
Worksheets("Bulk").Range("A3", Range("A3").End(xlDown).Offset(0, 11)).Sort _
        Key1:=Worksheets("Bulk").Range("A3"), _
        Key2:=Worksheets("Bulk").Range("B3")
           
           
'Fills in the formulas for the entire columns
'because when a new row is created the code isn't.
Range("C3", Range("A3").End(xlDown).Offset(0, 2)).FillDown
Range("E3", Range("A3").End(xlDown).Offset(0, 4)).FillDown
Range("G3", Range("A3").End(xlDown).Offset(0, 6)).FillDown
Range("I3", Range("A3").End(xlDown).Offset(0, 8)).FillDown
        
        
'Tidies the borders making the seperation of the locations more vivid
Worksheets("Bulk").Activate
  Range("A3", Range("A3").End(xlDown).Offset(0, 8)).Borders.LineStyle = xlNone
  ActiveCell.Offset(0, (10 - ActiveCell.Column)).Value = ActiveCell.Column
  Range("A3").Select
 
Do While IsEmpty(ActiveCell) = False
 If ActiveCell.Value <> Selection.Offset(1, 0) Then
    ActiveCell.Offset(0, 1 - (ActiveCell.Column)).Borders.Item(xlEdgeRight).Weight = xlThin
    ActiveCell.Borders.Item(xlEdgeBottom).Weight = xlMedium
      For x = 1 To 8
          Selection.Offset(0, x).Borders.Item(xlEdgeBottom).Weight = xlMedium
      Next x
    Selection.Offset(1, 0).Range("A1").Select

 Else:
     ActiveCell.Offset(0, 1 - (ActiveCell.Column)).Borders.Item(xlEdgeRight).Weight = xlThin
     Selection.Offset(1, 0).Range("A1").Select
 End If
Loop

Range("J1").Select
Range("J65536").End(xlUp).Select
Selection.Offset(0, -9).Range("A1").Select
Selection.Offset(0, 9).Range("A1").Clear
        
Application.ScreenUpdating = True

End Sub

Sub Package()
 
Dim CalculatedValue
Range("L2").Select
Do
    If ActiveCell.Value = "" Then
         CalculatedValue = Application.WorksheetFunction.RoundUp(ActiveCell.Offset(0, -1).Value / 1.5, 0) * 1.5 
      Select Case CalculatedValue
      Case 61.5, 63: ActiveCell.Value = 64.5
            Case 31.5, 33: ActiveCell.Value = 34.5
            Case Else: ActiveCell.Value = CalculatedValue
            End Select
        End If
        ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, -2))
End Sub

CalculatedValue = Application.WorksheetFunction.RoundUp(ActiveCell.Offset(0, -1).Value / .9, 0) * .9

CalculatedValue = Application.WorksheetFunction.RoundUp(ActiveCell.Offset(0, -1).Value / 1, 0) * 1

CalculatedValue = Application.WorksheetFunction.RoundUp(ActiveCell.Offset(0, -1).Value / 1.5, 0) * 1.5

Sub Package()
 
Dim CalculatedValue
Range("L2").Select
Do
    If ActiveCell.Value = "" Then
        CalculatedValue = Application.WorksheetFunction.RoundUp(ActiveCell.Offset(0, -1).Value / 1.5, 0) * 1.5
        ActiveCell.Value = IIf(CalculatedValue = 61.5 Or CalculatedValue = 63, 64.5, CalculatedValue)
    End If
    ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -2))
End sub

        ActiveCell.Value = IIf(CalculatedValue = 31.5 Or CalculatedValue = 33, 34.5, CalculatedValue)

 Range("A3","A22").Select

Cells(3,1,22,1).Select

Cells(3,1:22,1).Select

="A"&($C$20)+1

="A"&($C$20)+2

="A"&($C$20)+3

="A"&($C$20)+4

="A"&($C$20)+5

="A"&($C$20)+6

="A"&($C$20)+7

Attempting to count data that lies b/t 2 dates AND that also meets specific criteria in another column


1) Column P counts days between 2 dates and returns a numerical value
Code:

=DATEDIF(O2, A2, "D")

2) This counts how many cells from column P note that the 1st date and 2nd date are between 3 and 7 days of each other

Code:

=COUNTIF($P$2:$P$999, ">3")-COUNTIF($P$2:$P$999, ">7")

3) This counts how many cells in column L contain the words Clean or Short

Code:

{=COUNT(IF(($L$2:$L$999="clean")+($L$2:$L$999="short"),))

}

4) What I want is to be able to count how many dates that fall between 4 and 7 days of each other also have the words Clean or Short in the L column.

I figured out the formula that works when dates are less than or equal to 3 days apart

Code:

{=COUNT(IF(($P$2:$P$990<=3)*(($L$2:$L$999="clean")+($L$2:$L$999="short")),))}

But when I substitute

Code:

($P$2:$P$990<=3)

with

Code:

($P$2:$P$999>3)-COUNTIF($P$2:$P$999>7)

it doesnt work.

my best attempts:

1) Code:

{=COUNT(IF(($P$58:$P$999>3)-($P$2:$P$999>7),)*(($L$2:$L$999="clean")+($L$2:$L$999="short")),)}

(Returns 942... way off)

2) Code:

{=COUNT(IF(COUNTIF($P$58:$P$999,">7")-COUNTIF($P$2:$P$999,">14"),1)*(COUNTIF($L$2:$L$999,"clean")+COUNTIF($L$2:$L$999,"short")))}

(Returns 1... way off)

Ideally I would like to understand how to properly work COUNTIF formulas (i suppose its a syntax or order problem) so I dont have to spend 2 hours trying to figure it all out every time i hit a speed bump.

I am using Excel 97

EDITS: Trying to edit to Forum suggestions and rules. also included an example of what I am trying to do.

Hey,

I'm new to Excel VBA and I'm mainly just experimenting with some code right now.

This example found in John Walkenbach's 'Excel Programming for Dummies' works perfectly:

Code:

Sub FillRange()		
	Dim Count As Long	
	For Count = 0 To 19	
		ActiveCell.Offset(Count, 0) = Rnd
	Next Count	
End Sub

But when I try to make the random numbers controlled between integers 1 & 50, the activecell gets Offset 1 cell down. I can't figure out why there is an Offset with this code:

Code:

Sub FillRange()
    Dim Count As Integer
    Dim Low As Integer
    Dim High As Integer
    Low = 1
    High = 50
    For Count = 0 To 10
        ActiveCell.Offset(Count, 0) = R
        R = Int((High - Low + 1) * Rnd() + Low)
    Next Count
End Sub

If someone can take a look at this, I would greatly appreciate it

Thank you sooo much
Bob

I have the following 2 formulas that I'm trying and all I get is #####. I only want to count cells that have an X in them.

Code:

=IF(activecell.address="x",COUNTA(V12:V5093),"")

and

Code:

=IF(activecell.address = "", "",COUNTA(U12:U5093))

When I use the following code, it works correctly, but it also counts all values and I only want to count X's.

Code:

=COUNTA(N12:N5093)

Thanks in advance for your help.

Hello all, Please Help!

I am trying to figure out how to use the code below to only show certain worksheets after the I Accept button is clicked instead of all worksheets in the workbook.

disclaimer Worksheet named Disclaimer:

Code:

Private Sub Acceptbtn_Click()
HideSheets xlSheetVisible
If Not objCurrent Is Nothing Then
objCurrent.Activate
objSelection.Select
Else
Sheet1.Activate
End If
End Sub

Module1:

Code:

Public objCurrent As Object
Public objSelection As Object

Sub HideSheets(varVisible As Variant)
Dim Sht As Worksheet
If varVisible = xlSheetVeryHidden Then
With Disclaimer
.Visible = xlSheetVisible
.Activate
End With
End If
For Each Sht In ThisWorkbook.Worksheets
If Sht.CodeName <> "Disclaimer" Then
Sht.Visible = varVisible
End If
Next Sht
End Sub

ThisWorkbook:

Code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Set objCurrent = ActiveSheet
Set objSelection = Selection
HideSheets xlSheetVeryHidden
End Sub

Hi

I have a combo box on my work sheet which I want to be able to clear. (not remove the items, just make it blank until the user selects another item in the list).

This works
Code:

  ActiveSheet.Shapes("comboBox").Select
  Selection.ListIndex = 0

But, can I do it without selecting something like.... (obviously this doesn't work)
Code:

Dim cbo As ComboBox
Set cbo = ActiveSheet.Shapes("comboBox")
cbo.ListIndex = 0

Also, am I better of using the activex controls instead???

Hi all,
I'm trying to customize how my time gets display
Currently using,

Code:

  .Offset(, 2).Value = Now

But this does not show AM/PM.

I've tried this
Code:

   .Offset(, 2).Value = Now(MM, DD, YY, HH, MM, AM / PM)

But that's not valid .

Any otherway to do this?

Thanks,

bdb

Good Afternoon All,
I'm trying to add a top border to Columns L through N after filtering on Column "B" and customize to just the rows that appear with "Total" in its name.

The problem is, it will INSERT 3 new columns THEN add the Top-border to the rows as opposed to the already in place L through N columns

How can I just have it add the top border to the range of L through N on all rows that contain the word "Total"?
Code:

'Filter on "Total" and add borders
    Range("B1").Select
    Selection.Autofilter
    Selection.Autofilter Field:=2, Criteria1:="=*Total*", Operator:=xlAnd
    Range("L:N").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    Selection.Autofilter
    End With

EDIT:
Found the problem :-)

I use the following vlookup to show whether a team name is within a set range:


Code:

Command	CST1		CST2		CST3	
Team	EU1	√	EU7	√	FR6	√
	EU3	√	EU9	√	FR7	√
	EU4	√	EU10	X	FR8	√
	RA1	√	EU11	√	FR9	√
	RA2	√	EU12	X	FR10	√
	RA3	√	EU13	√	FR11	√
	DN1	√	EU14	X

The code to produce the above (where f8 = cell value to lookup in my sheet f8 = EU1 )
Code:

=IF(ISNA(VLOOKUP(F8,sectionnames,1,FALSE)),"X","√")

The range the vlookup looks within
Code:

sectionnames range:
RA3

RA2

RA1

FR9

FR8

FR7

FR6

etc..

This works fine unless someone adds a team name uncapitalised as FR9 below:

Code:

RA3

RA2

RA1

fr9

FR8

FR7

FR6

How can i get around this problem? Can i make the vlookup look for uncapitalised versions of the team name? or create a macro that capitalises all values within a column?

Thanks for any help

I have two defined ranges, and they keep changing to include the workbook name.

XV2 =
Code:

=OFFSET('Last So Many'!$A$3,0,COUNTA('Last So Many'!$4:$4)-'Last So Many'!$E$1,1,'Last So Many'!$E$1)

YV2 = Code:

=OFFSET('dynamic-names-and-charts.xls'!XV2,1,0)

Even though I put in 'Last So Many' when I edit YV2 it still continues to use the workbook name instead of the worksheet. I'm getting errors with this because of the naming.

I tried changing to the worksheet before defining the name, but no luck. I was afraid if I kept it was the workbook name in the reference if it will update when saved as a new file or not.

Dear all,


i have big macro with if statement and a variable that the position and selection of the active cell depends on. Everything is working perfectly and i want to add some extra conditions in order to select the position of the active cell because i have some rows which include 0 values.I should mention once again that the active cell's address depends on the result of the previous if statements and a variable so the cell that i need to check if its value = 0 has to be expressed with the active cell's position.
I also don't want to move active cell before i know the result. After i know if the result of the cell under the active

So what i want to say is:

Code:

If Cell(ActiveCell.Row +1, Active.Cell.Column) = 0 Then
   ActiveCell.Offset(1, 0).Range("A1").Select
ElseIf Cell(ActiveCell.Row +1, Active.Cell.Column+1) = 0 Then
   ActiveCell.Offset(1, 1).Range("A1").Select
ElseIf Cell(ActiveCell.Row +1, Active.Cell.Column-1) = 0 Then
  ActiveCell.Offset(1, -1).Range("A1").Select

So how can i write this Cell(ActiveCell.Row +1, Active.Cell.Column)?

Do you think you could help me??



Thank you in advance!!!

Guys and Gals,

Can I use the following function result and change it to text? Code:

=ADDRESS($C$23,2,,,"mysheet")

the result of that function, for me, is this: Code:

mysheet!$B$10

what I want to do with that literal outcome is plug into the next function, which is this: Code:

=MATCH($E$8,aboveResult:aboveResult)

So in the cell it looks like: Code:

=MATCH($E$8,$F$23:$F$24)

but I just get this as a result: Code:

#N/A

Is there a way to fix that problem? A text conversion function? I've looked through the help files but can't find what I need. thanks!

Hi All,

Hopefully this is a simple question. I have a cell - C80 - in which I have the formula:

Code:

=B72

I went to copy this down to C81 and it changes to:

Code:

=B73

I then added a $ sign by the row number so it now changes to:

Code:

=B$72

i.e. no change.

I want it to change to:

Code:

=C$72

Code:

=D$72

and so on.

Can anyone help?

Thanks
dvent

Code:

A1 "5"
B1 ">" or "<"
C1 "2"
D1 "=IF(A1&B1&C1,"True","False")"

...is not working, returning #VALUE! in D1. I notice that the evaluation appears to be (correctly) converting it to a string:

Code:

IF("5>2","True","False")

...is there any way I can have it evaluate properly? I am aware that I can do something like that shown below, but for obvious reasons am reluctant to do so!

Code:

=IF(B1=">",IF(A1>C1,"True","False"),IF(B1="<",IF(A1<C1,"True","False")))

hello,

I have an example code which if attached to a button in the target worksheet returns the correct result.

Code:

Worksheets("Before Replenishment").Range("K1").NumberFormat = "@"
Worksheets("Before Replenishment").Range("K1") = Worksheets("Before Replenishment").Range("J1").Text
numdate = Application.CountIf(Sheets("Before Replenishment").Range("I2", Sheets("Before Replenishment").Range("A2").End(xlDown).Offset(0, 8)), "*" & Range("K1") & "*")

MsgBox numdate

However if I place this code on a button in another worksheet it doesn't return the right result?


Code:

Worksheets("Before Replenishment").Visible = True
Worksheets("Before Replenishment").Activate

Worksheets("Before Replenishment").Range("K1").NumberFormat = "@"
Worksheets("Before Replenishment").Range("K1") = Worksheets("Before Replenishment").Range("J1").Text
numdate = Application.CountIf(Sheets("Before Replenishment").Range("I2", Sheets("Before Replenishment").Range("A2").End(xlDown).Offset(0, 8)), "*" & Range("K1") & "*")

MsgBox numdate

Any help on this and I would be very grateful.

Thanks

Alex

Hi - I am trying to import data from an external XLS spreadsheet. The external sheet has four columns, an alphanumeric code, a description, and two prices (decimals).

When I configure and run the import, everything is imported except any codes which are not digits only.

I.e. the row;

Code:

1000012   Test record   0.12  0.15

is imported just fine, but;

Code:

10001AB   Second record   0.23  0.35

will only be imported as;

Code:

          Second record   0.23  0.35

I have tried all sorts of things to figure this out but am completely stuck.

Any ideas or suggestions?

Thanks in advance!

Hi all,

I have encountered a Run-Time '1004' error which says my selection is not valid on the Insert line(bolded) when my macro tries to insert a cut row into row 2.

Please help!!!

Code:

WS2.Range("H" & d).Rows.Offset(1, 0).Select
WS2.Range("H" & c).EntireRow.Select
Selection.Cut                       
WS2.Rows(d).Select                
Selection.Insert Shift:=xlDown

Is it possible to change this code so it knows where to look for Company List.xls?

Code:

=OFFSET('Company List.xls'!$A$1,0,0,COUNTA('Company List.xls'!$A:$A),1)

Company List.xls is stored here

Code:

Environ("USERPROFILE") & "\Desktop\VAT Books & Invoices\Company List.xls

I cant seem to make it work

Many Thanks

I am trying to copy and paste 6 cells in a row on one worksheet to 6 cells in a row on another work sheet.
I want to do this in a MACRO (open to better ideas - with in my skill set), assigned to a button on the first worksheet.
The location for the Pasting is ALWAYS starting in the same column on the second worksheet and in in the FIRST empty cell in that column.
So I recorded
Code:

Selecting the required cells from the first worksheet
Ctrl + C (to Copy)
Clicking on the second worksheet
selecting in the top row cell of the column I wan to start the paste
End + Down Arrow (to navigate to last cell in that column with data)
Down Arrow (to go to the first empty cell)
Edit/Paste Special/Values
Click in first worksheet and place my curser where I I want it to end
Stop Recording

Problem is, this never ends up in the first empty cell, it ends up in what ever cell it was recording.
So I am guessing the macro is recording the final selection and NOT the keystrokes that got me there.

Any suggestions on how to improve my Macro, or a better solution to this.

Thanks

Dave

BTW: Here is the VBA from the MACRO
Code:

Sub Update()
'
' Update Macro
'

'
    Range("E1:K1").Select
    Selection.Copy
    Sheets("Totals").Select
    Range("B1").Select
    Selection.End(xlDown).Select
    Range("B3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Today").Select
    Application.CutCopyMode = False
    Range("E1").Select
End Sub

Hi,

I'm trying to SUBSTITUTE one of two text values depending on what is in the cell to the left.

Formula I've tried is:

Code:

=IF(SEARCH("XXXX",B1),SUBSTITUTE(B1,$G$1,$H$1),(SEARCH("YYYY",B1),SUBSTITUTE(B1,$G$2,$H$2)))

Each half works separately
Code:

=IF(SEARCH("XXXX",B1),SUBSTITUTE(B1,$G$1,$H$1)

or
Code:

=IF(SEARCH("YYYY",B1),SUBSTITUTE(B1,$G$2,$H$2)

but when I try to combine them I cannot get it to work.

Any ideas?

Thanks

I usually create a macro to copy the data and send it to a holding area, and the pivot would pick up the data from the dynamic range.
I tried using this method from this link:
HTML Code:

http://groups.google.com/group/microsoft.public.excel.programming/browse_frm/thread/fa9c4f90d179801e/51180a075dc4aab5?lnk=gst&q=one+pivot+many+ranges#51180a075dc4aab5

I tried using this method to switch the data to a pivot table:
Step 1: Create a dynamic ranges named Pivot1 and Pivot2 for Sheet2
Code:

=OFFSET(Sheet2!$A1,0,0,COUNTA(Sheet2$A:$A),2)

and
Code:

=OFFSET(Sheet3!$A1,0,0,COUNTA(Sheet3$A:$A),2)

Step 2: Created a dynamic name called Data:
Code:

=INDIRECT(Sheet1!A1)

Then tried to create a pivot table using the named range Data, but it failed; I got a message saying the reference is not valid.
Can someone tell me what am I doing wrong?

Xrull