Posts by naxos

Hey again, I am sure this is pretty simple, i just cant figure out why i am getting an error on this. I want to print out the first page of every worksheet in the workbook. so I am using this routine:

Sub PrintPage1()
Dim ws As Worksheet
    For Each ws In Worksheets
        If ws.Name Like prefix Then
             ws.Activate
             ws.PrintOut( 1, 1) 'here i get "expect =" syntax error
            
        End If
    Next
End Sub

As my comment notes...i am getting a syntax error, msdn examples use the same format i am....what am i doing wrong? thanks!

ok, one last quick, question about macro interfaces, with the command bar? say i want to add a new buttont to the right of the existing button in the code. How can i do that? so far i only get 2 buttons that are one on top of the other.

:thanx:

Heh, it is always nice to have deconstructors

Thanks once again!

Thats fantastic! Thanks

I like this a lot! I have a questions though.

How can i get the customcommand macro to execute when the template is opened, so that the buttons will be there for everyone, even if they are not familiar with macros

Thanks again!

Oh Ok, thanks for the tip on using the formula! works great

Thank you once again!

Worked like a charm! Thank you so much

Hmmm, I see that it worked on your spread sheet. However I am having trouble with the data sheet that I have.

I seem to be getting a Data Mis-Match

here is an example of the data I will be analyzing.

Hello again,

I am trying to write a sub that would look at a 2 rows, one has conesutive numbers ie. 1, 2, 3,4,5,6,7 and under that row i have another row (duh!) that has numbers.

What I want to do (so i can see a nice graph of averages over time) is take the mean of each of these 5 numbers, so the first 5 would be put in row "22" in the first slot, and the next 5 6-10 would be placed in the next cell on row 22. here is what I have that doesnt seem to be working so hot

Sub CorrectLatencyAnalysis(trials As Integer)
Do While i >= trials
Dim Sum As Variant
Dim Divisor As Integer
Dim Average As Variant
Divisor = 0
Dim curRange As Range
Dim cel As Range
curRange = Range("B14") 
Range(curRange, Range("B14")).Offset(i, 0).Select
i = i + 5
curRange = curRange.row + 5


For Each cel In Selection
        If cel.Value <> 0 Then
            Sum = Sum + cel.Value
            Divisor = Divisor + 1
        End If
Next cel
Average = Sum / Divisor
    
FillRow(Range("A22")) = Average
Loop
End Sub

Here is how i am calling the function, Theoretically the last cell in the row of consecutive integers would give us the correct number of "trials"

CorrectLatencyAnalysis (Range("A13").End(xlToRight).Value)

Thanks much!

How can I make the template with my macro have a worksheet that has Buttons, one would initilize a certain macro, another few might be graphing options that would activate other subs. I did a quick google search and didnt find much of a tutorial or anything really.

Thanks

I was thrown off by the not equal as well, I was used to != being the operator but here in VB it is "<>"

Hmm Derk, I'm trying to use your formula, I have it typed in like this

=SUMPRODUCT((14:14<>0)*14:14)/COUNTIF(14:14,"<>0")

I am using row 14 for the average and the box with the formula in it says #VALUE!

Not sure what im doing wrong here, but the row does have numbers and zeros in it.

As for your second question the I want to remove all the dashes from Row 17 and have the "collapsed" version put into row 20 starting with B20 (there is a row title in A20. Thanks

jmhans, I put your code for the nonzeroaverage into work and passed it Range(14:14) using this line:

'Range("J8").Value = NonZeroAverage(Range("14:14"))

but i kept getting an overflow error from the NonZeroAverage, I changed both your variables to doubles, but it still happened, btw the numbers we are working with are not that large THANKS!

Hello Again, I want to be able to run through a certain row that contains 0's and other numbers, I want an average of the non-zero containing numbers, is there any way to do this easily with a formula?

On a similar note, i have a row that contains numbers and the '-' char as a placeholder, i would like to take that whole row, remove all of the '-' and paste the new numbers into a new row, where the numbers are contiguous, (ie. i dont want blank cells whereever the '-' was taken out, i would want it collapsed

Thanks a million in advance! You guys are lifesavers!

Incredible!

jmhans It works BEAUTIFULLY! Thank you so much!

It certainly is fighting

What should i be passing this new function, i have been passing it an int before, should i be passing "A15" where 15 would be the row i want filled?

right now i am calling it like this

ActiveSheet.Range(FillRow(15).Address) = (ErrorTime - LevOut)

Hmm It seemed to run a little further. I got a Run time error with that new function, of "Object required"

Debug is citing this line

Set FillRow = Range("A" & CStr(row)).End(xlToRight).Offset(0, 1).Value

But it did start to fill in some values on the first empty row where i wanted it to. But didnt get past the first line of data

Here is my current project, maybe it would help to see the error I am getting, Just run CreateSheets and that should run through all the subs on its own.

Thanks again

Ok, now its not crashing due to syntax (i think im so used to c++, i need my void functions!) But it is having a runtime error '1004' "Application-defined or object-defined error.

and it cites this part of the code

Function FillRow(row As Integer) As Range
    If Len(Range("A" & CStr(row))) = 0 Then
        Set FillRow = Range("A" & CStr(row))
    Else: Set FillRow = Range("A" & CStr(row)).End(xlToRight).Offset(0, 1).Value 'This line Errors
    End If
End Function

By the way, why cant I simply use the Range("A16").End(x1ToRight).Offset(0,1) alone and without a separate function? will that not do the same thing?

Lets see, jmhans, that didnt seem to do the trick, I am getting the error "Expected =" but i changed my routine to typcasting the string as cstr anyway.

Thomach, I am trying to make this batch process a lot of data, so entering the row and answer is not much of an option.

Is there any way to make this work? Thanks guys

Ok, So i have tried to make this into a function within my Macro...I tried it as both a function and a sub. here is the function i wrote

Function FillRow(row As Integer, Answer As Variant)
    If Len(Range("A" & row)) = 0 Then
        Range("A" & row) = Answer
    Else: Range("A" & row).End(xlToRight).Offset(0, 1).Value = Answer
    End If
End Function

and when i try to call it from other code by using this line

FillRow(18,Side)

where 18 is the row i want to insert into the last unfilled cell and Side is a variable that i want to place there.