Posts by pangolin

Re: Compare two columns in different worksheets

you can use something like this

If cell.Value <> "" Then


'Put your other code here




End If

Re: Automatically fill in data based on multiple criteria from another spreadsheet

put this formula in Cell B2 and copy across down

=IF(B$1>=EDATE(INDEX('Sheet 2'!$C$2:$C$6,MATCH($A2,'Sheet 2'!$A$2:$A$6,0)),2),INDEX('Sheet 2'!$B$2:$B$6,MATCH($A2,'Sheet 2'!$A$2:$A$6,0)),"")

Re: Extract all rows that contain a certain value

see if this code helps

Sub copypaste()
Dim unionrange As Range
For Each cell In ActiveSheet.UsedRange
If cell.Value = "dd" Then
x = x + 1
If x > 1 Then
Set cr = Range("A" & cell.Row)
Set unionrange = Union(unionrange, cr)
Else
Set unionrange = Range("A" & cell.Row)
End If
End If
Next cell
unionrange.EntireRow.Copy Destination:=Sheets("sheet2").Range("A1")
End Sub

Re: Find the merged cell

see if this code helps

Sub mergecells()
For Each cell In ActiveSheet.UsedRange
If cell.mergecells Then
Set myrange = Range("A1", "A" & cell.Row - 1)
myrange.EntireRow.Delete
Exit Sub
End If
Next cell
End Sub

Re: IF two criteria are met in the row THEN SUM but, only if it is between a date ran

this is one approach

for hours worked in Cell B19

=SUMPRODUCT((D2:D12)*((A2:A12)=C19)*(MONTH(J2:J12)=MONTH(A19)))

where the Department is in Cell C19

other formula can be built on similar lines

Re: Compare two columns in different worksheets

see if this code helps

Sub matchcopy()
Dim myrange1 As Range, myrange2 As Range
On Error Resume Next
With Sheets("Sheet1")
Set myrange1 = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
End With
With Sheets("Sheet2")
Set myrange2 = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
End With
For Each cell In myrange1
If WorksheetFunction.IsError(WorksheetFunction.Match(cell.Value, myrange2, 0)) = True Then
cell.Offset(0, 15) = "NO"
cell.Offset(0, 16) = "NO ACCESS"
Else
cell.Offset(0, 15) = "YES"
End If
Next cell
End Sub

Re: Cross check 11 columns on sheet 1

try the FIND or SEARCH function?? see the help files and read posts on how to use wildcards

Re: Compare two columns in different worksheets

why do you want code???you can do it with a simple MATCH formula

see the help files or some of the other posts on this forum for more help

Re: Del Specific Columns via a Macro

see if this code helps

Sub delcol()
Dim unionrange As Range, mydelrange As Range
Sheets("Which Col to Del").Select
Set myrange = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each cell In myrange
x = x + 1
Set mydelrange = Sheets("Main Data").Range(cell.Value & 1).EntireColumn
If x = 1 Then Set unionrange = mydelrange
If x > 1 Then Set unionrange = Union(unionrange, mydelrange)
Next cell
Sheets("Main Data").Select
unionrange.Delete
End Sub

Re: Control Radio button from a Radio Button in another group

one way is to use the "Enable"property when you click on an option button

see this code below for your options buttons 3 & 4 of Group 1

[vba]
Private Sub OptionButton3_Click()
Sheets("Sheet1").Range("F1") = 30
OptionButton5.Enabled = False
OptionButton6.Enabled = False
OptionButton7.Enabled = True
OptionButton8.Enabled = True
End Sub
Private Sub OptionButton4_Click()
Sheets("Sheet1").Range("F1") = 40
OptionButton5.Enabled = False
OptionButton6.Enabled = False
OptionButton7.Enabled = False
OptionButton8.Enabled = True
End Sub
[/vba]

you can set similar code for your other two option buttons
hope this helps

Re: Monthly customer comparison - numbers are off

this is one approach

=SUM(IF(ISERROR(MATCH($B$2:$B$6,$H$2:$H$6,0))=FALSE,1,0))

where the first list is in B col and second list is in H col

note this is an array formula and needs to be conformed by Ctrl+Shift+Enter

Re: 3 Condition countif Formula

is this what you are looking for

=COUNTIFS($A$5:$A$38,$D$4,$E$5:$E$38,$C$3,$N$5:$N$38,"yes")

Re: Need help with creating a dependant drop down menu that performs calculations

for linking List to List you will have to work with Named Ranges and accordingly link the same via data Validation

in the attached worksheet I have worked out the first field for you

for seeing the named ranges goto Formulas>>Name Manager

for seeing how the list have been linked goto Data>>Data Validation>>and under 'allow' select List

also see how a new table giving List Names have been created in Sheet02...Once you have understood that part the other requirements in your post are repitition which you can pretty much do yourself

hope that helps

Re: Sumifs criteria if date is in list

this is one approach

=SUMPRODUCT(($D$4:$D$31)*(WEEKDAY($B$4:$B$31,2)=ROW(A1))*ISERROR(MATCH($B$4:$B$31,holidays!$C$3:$C$15,0)=FALSE))

note the use of WEEKDAY function

hope that helps

Re: Barcode reader with timestamp product selling sorted per day

try the SUMIFS or SUMIF or SUMPRODUCT functions...see the help file

also check out some of the various (and numerous) posts on these functions in this forum

Re: Copy Row Based On Cell VAlue To Another Worksheet VBA

you can use this code

Sub cpaste()
Dim myrange As Range
Sheets("List").Select
Set myrange = Sheets("List").Range("Q1", Range("Q" & Rows.Count).End(xlUp))
For Each cell In myrange
If cell.Value = "Yes" Then
lr = Sheets("Archived").Range("Q" & Rows.Count).End(xlUp).Row
cell.EntireRow.Copy Destination:=Sheets("Archived").Range("A" & lr + 1)
End If
Next cell
End Sub

Re: Add column with data from another sheet where specific values match

you can use one of the LOOKUP formulae or INDEX/MATCH...see the help files for more info

Re: Formula to move table column into raw lable

put this formual in Cell G4 and copy paste

=IFERROR(INDEX($C$4:$C$20,SUMPRODUCT(ROW($A$4:$A$20)*(($A$4:$A$20)=G$3)*(($B$4:$B$20)=$F4))-(ROW($C$4)-1)),"")

Re: Correlation on different length sets of data?

I believe so...typically in an array the function CORREL ignores a value if its corresponding value in the other array is empty (although not zero)

Re: Change TAB Name of a Single Sheet with the Date

Query 1 can be resolved like this

Private Sub Workbook_Open()
Sheet1.Name = Sheet1.Range("B3").Value
End Sub

you need to put this code under VBExplorer>>Microsoft Excel Objects >> "ThisWorkbook"

Query 2 is returning an error because the dates in the Data worksheet have end of month days while cell B3 have day as 01

hence you can use this formula

=SUM(LOOKUP(EOMONTH(B3,0),Data!$A$3:$B$14,Data!B$3:B$14))/1000