Posts by elf108

I know If IsEmpty(s.Offset(0, -2))

but how to check IsNotEmpty ???

thanks in advance !!!

how to i find if a cell is empty

so that i can do if cell empty do wat...

thanks in advance

I need to check,shld be easy tough

becos i have a table which i will hide
the zero value row...however it is in
a pivot table format,so when i hide certain
row the header will be hide as well and
i will have no clue at it is under and have
to unhide to check it...

preharps someone can help me ?
details inside,simple straight forward

thanks that was fast.

and easy too

Need help in getting the active sheetname
so that i can verify it in my code and
do different code base on the sheetname
thanks in advance

record the file open process
and close the file

this is wat i can think of so call
runnnig macro not opening workbook

as above.
because i want to do a loop base on the
sheetnames

say
var(1)="asia"
var(2)="america"
i=1

then my code will be
for num=i to 2
sheets(var(i)).activate
my code
next

i got another code but don suit my taste
because i want to define the sheet name
myself,and the code strangely show
up module1 at the last of the sheetname
which i wonder how the hell it appear

Dim shtNames()
Dim Counter
Dim numShts
Dim sht
Dim ActSht
Dim ActShtNum

Counter = 1
numShts = Sheets.Count

ReDim shtNames(1 To numShts)

For Each sht In Sheets
shtNames(Counter) = sht.Name
If ActiveSheet.Name = sht.Name Then
End If
Var = shtNames(Counter)

ActSht = ActiveSheet.Name
ActShtNum = Counter

Counter = Counter + 1
MsgBox Var
Next sht

thanks alot...although without the
getting A B C at the top...i can use numbers actually
thanks for the code,yes i wanted to
select the entire column after using find
command

using that i get 1 for A...
but how do i actually implement the 1
into A ???

means if i get 1...how do i use
range("A10").copy since the col i get
is in number format not alphabets
thanks

hi there,

Errorhandler3:
MsgBox "No SHIPPED in " & cellname & " under UnC Demand. Press OK to Contiune... "
Resume fine

I like to add color to my wording in the MsgBox. I want the words "No SHIPPED in" to be in red in color and & cellname & in green in color. How can i do that??

Thank you very much...

in this code if the error occur twice it
will error pop up ,code stops.

i am thinking how to loop it in a way such as whether it is error free or error it can
go through the for loop,because i have a
code that loops based on user input...if error then display the string he typed in.
if no error then continue in the loop until
error comes again.

My purpose is that i have a user key in
say Aug,then i will activate from sheet1 to sheet20 (diff products) find the cell Aug for every sheet,if the sheets does contain Aug,then display the sheetname and continue for the next sheet until end of file.

thanks:)

may i know how many empty cells
are there between a certain range that
a vba can count ?

my code cannot do it seems the for loop
over do it

Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
For a = 2 To sheet1lineno
Selection.FindNext(After:=ActiveCell).Activate
Row = ActiveCell.Row
row2 = Row - 1
Range("U" & Row).Value = "=T" & Row & "/T" & row2
Next

solved after some brain storming

Dim pit
On Error Resume Next
For Each pit In ActiveSheet.PivotTables("PivotTable1").PivotFields("product").PivotItems
pit.Visible = False
With ActiveSheet.PivotTables("PivotTable1").PivotFields("product")
.PivotItems("bb").Visible = True
End With
Next pit
End Sub

it works !!!
but it will come to an error where everything are been turn off.
how do i set a factor true in this case ?
previously i use this but now can't
.PivotItems("abc").Visible = True

wow thanks u been a great help !

thanks but i pasted that function code
it shows red text and cannot run

it's would be great if i can figure out
an sheet exist or not,if yes do things i want.else show msgbox

out of range error

no i am thinking if i want to access the first sheet, error,go proceed to second sheet ,error,finally go to third sheet.

tis is wat i'm thinking

thank you

why does the error appear even when i tell the program to go to sheet3 ?
Sheets(jiji).Activate --this is the error
suppose to proceed further down,this code is tested with default excel book1

On Error GoTo sheet2
Sheets(kjjj).Activate
Range("A1:AL1").Select
Selection.Interior.ColorIndex = 6
Range("A1:AL1").CurrentRegion.Select
Selection.NumberFormat = "0"

sheet2:
On Error GoTo sheet3
Sheets(jiji).Activate
Range("A1:AL1").Select
Selection.Interior.ColorIndex = 6
Range("A1:AL1").CurrentRegion.Select
Selection.NumberFormat = "0"

sheet3:
Sheets("sheet1").Activate
Range("A1:AL1").Select
Selection.Interior.ColorIndex = 6
Range("A1:AL1").CurrentRegion.Select
Selection.NumberFormat = "0"

ok,if there any command to turn off all to false instead of settting them false one by one,so that i can simply off everything and turn on wat i want using my code.

So that even i don know say a,b,c,d is inside,i can simply turn off all then set a to true...so the code won't get too long like
.PivotItems("a").Visible = False
.PivotItems("b").Visible = False
.PivotItems("c").Visible = False
.PivotItems("d").Visible = False

instead i would like to have
.pivotitems(ALL).visible=false -something i like to have very much !!! :o
.pivotitems("a").visible=true

with this code

With ActiveSheet.PivotTables("PivotTable2").PivotFields("Data Load")
.PivotItems("MRP LOAD").Visible = False
.PivotItems("NBA").Visible = False
.PivotItems("CAPACITY").Visible = False
.PivotItems("GLOBAL BUILD").Visible = True

however i need to have the parameters like NBA CAPACITY to turn off and on.
is there any way to turn off all then turn on 1 base on the i specify...otherwise i have to set everything to false and if there's new things inside i might miss it