Posts by googlehoff

googlehoff · Sep 25th 2006

Re: Copy And Paste Multiple Cells In Range Through Vba

Problem solved. I needed to take out the end for statement.

sorry for the post:music:

googlehoff · Sep 25th 2006

Below is a sub to lookup andtransfer certain cells that fit a criterion. I am trying to get it to run for all cells in the range of 20 cells specified by the 1to 20 range. This only works for the lower most cells. Any hep or better way to approach?

Thanks

Code

Dim i As Integer
    
        For i = 1 To 20
        
If Sheets("Main").Range("o" & 56 - i) <> "" Then
    Sheets("main").Select
    Range("S" & 56 - i).Select
    Selection.Copy
    Sheets("main").Select
    Range("j" & 26 - i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("main").Select
    Range("r" & 56 - i).Select
    Selection.Copy
    Sheets("main").Select
    Range("k" & 26 - i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 Exit For
 End If
 
Next i

Display More

googlehoff · Jun 9th 2006

Re: VBA recognizing cell value

Thanks Swan, it is working now!

googlehoff · Jun 9th 2006

Re: VBA recognizing cell value

Update on the problem:

After more tinkering, the problem is not recognizing thecell value itself, but rather the cell value that was picked from a drop down list.

The previous version had teh drop down, but I added insurance and it caused the problems.

???

googlehoff · Jun 9th 2006

I have a VBA chunk that recognizes a value in cell 'E9' and based off of that it runs other macros. I added an additional "elseif" and now it will not recognize any value in E9 and returns the message box.

The new lines are bolded.

Any suggestions or comments?

Code

Sub NetWorthEntry()
'
' NetWorthEntry Macro
' Macro recorded 6/7/2006 by adam
'
If Sheets("NW Entry").Range("E9").Value = "Retirement Accounts" Then
    Application.Run "Retirement"
ElseIf Sheets("NW Entry").Range("E9").Value = "Bank Accounts" Then
    Application.Run "Bank"
ElseIf Sheets("NW Entry").Range("E9").Value = "Investment Accounts" Then
    Application.Run "Investment"
ElseIf Sheets("NW Entry").Range("E9").Value = "Annuities" Then
    Application.Run "Annuities"
[B]ElseIf Sheets("NW Entry").Range("E9").Value = "Insurance" Then
    Application.Run "Insurance"[/B]
ElseIf Sheets("NW Entry").Range("E9").Value = "Real Estate" Then
    Application.Run "Real"
ElseIf Sheets("NW Entry").Range("E9").Value = "Business/Partnerships" Then
    Application.Run "Business"
ElseIf Sheets("NW Entry").Range("E9").Value = "Personal Property/Autos" Then
    Application.Run "Personal"
ElseIf Sheets("NW Entry").Range("E9").Value = "Liabilities" Then
    Application.Run "Liabilities"
Else
    MsgBox "Why aren't I working!!!"
End If






'
End Sub

Display More

googlehoff · Jun 9th 2006

Re: Cell Values running macros

the title of this thread was truncated for some reason. sorry

googlehoff · Jun 9th 2006

I am working with a simple net worth workbook. People are able to enter in an entry sheet and it is populated in another sheet by a macro specific to the category that they chose.

It has always worked fine, but now it doesn't. The individual macros work fine, but i only recieve the message box response when i run the following:

Code

Sub NetWorthEntry()
'
' NetWorthEntry Macro
' Macro recorded 6/7/2006 by adam
'
If Sheets("NW Entry").Range("E9").Value = "Retirement Accounts" Then
    Application.Run "Retirement"
ElseIf Sheets("NW Entry").Range("E9").Value = "Bank Accounts" Then
    Application.Run "Bank"
ElseIf Sheets("NW Entry").Range("E9").Value = "Investment Accounts" Then
    Application.Run "Investment"
ElseIf Sheets("NW Entry").Range("E9").Value = "Annuities" Then
    Application.Run "Annuities"
ElseIf Sheets("NW Entry").Range("E9").Value = "Insurance" Then
    Application.Run "Insurance"
ElseIf Sheets("NW Entry").Range("E9").Value = "Real Estate" Then
    Application.Run "Real"
ElseIf Sheets("NW Entry").Range("E9").Value = "Business/Partnerships" Then
    Application.Run "Business"
ElseIf Sheets("NW Entry").Range("E9").Value = "Personal Property/Autos" Then
    Application.Run "Personal"
ElseIf Sheets("NW Entry").Range("E9").Value = "Liabilities" Then
    Application.Run "Liabilities"
Else
    MsgBox "Why aren't I working!!!"
End If






'
End Sub

Display More

Any suggestions or possible reasons?

Thank you very much in advance

googlehoff · Mar 31st 2006

Re: SUM lookup returns

Thanks! I guess I was trying to make this too complicated.

Bob, you are a wonderful human being!

googlehoff · Mar 31st 2006

Before I get started, I know that this is a simple pivot table task and there is no reason to expect an easy solution to the issue. The solution is for a excel hating coworker who needs as little work on her end as is possible.

I have a master worksheet That contains names and account numbers. I also have a worksheet that contains account numbers and withdrawals. I am trying to use a combination of vlookup, match, sum, etc. to look up the account numbers in the withdrawal sheet and sum up the withdrawls and display them in the master.

any suggestions or should I force my coworker to learn how to use pivot tables?

Thanks

googlehoff · Feb 22nd 2006

Re: Filling Cells with Formulas

works wonderfully! Thank you very much!

googlehoff · Feb 22nd 2006

Re: Filling Cells with Formulas

Thanks for the suggestions, but I guess the problem I am having does not invlove those references. Referring back to the code, I am trying to have the '5' in the hlookup increase as I fill or the equivilant.

googlehoff · Feb 22nd 2006

Re: Filling Cells with Formulas

Removing those brings error returns because the reference for the hlookup is then missing

googlehoff · Feb 22nd 2006

I am trying to fill a range of 400 cells with the following formula (not VBA but thought it would help to tag it):

Code

=IF(VLOOKUP($K$4,$AE$3:$AF$53,2,FALSE)=1,HLOOKUP($K$4,Monthly!$H$1:$CB$265,5,FALSE),
IF(VLOOKUP($K$4,$AE$3:$AF$53,2,FALSE)=0,HLOOKUP($K$4,Quarterly!$H$1:$AD$265,5,FALSE),
IF(VLOOKUP(Comparison!$K$4,$AE$3:$AF$53,2,FALSE)=2,HLOOKUP(Comparison!$K$4,Annually!$I$1:$M$265,5,FALSE),"")))

I would like to know if there is something that I can do to have the hlookup row reference increase as I fill so as I drag down the next cell returns the corresponding cell in the array.

Thanks
Hoff

googlehoff · Jan 17th 2006

Re: SUMIF Across Multiple Pages

Sorry, I have been out of town and have not checked the board. The problem that I was having involed exported data from another application.

I didn't realized it at first, but there were 2 or three spaces afetr the lookup values. I just had to trim() the values and it worked.

Check your cells palace and see if everything is the exact same

googlehoff · Jan 10th 2006

Re: SUMIF Across Multiple Pages

Thanks, but i figured it out.

googlehoff · Jan 9th 2006

I am using a SUMIF to reference an array that contains the range and the sum_range from a sheet that contains the criteria. It doesn't work. I only get 0.

When I use all three inputs off of the referenced sheet, it works. Is this the only way it will work, or can I somehow make life easier with a different way of approaching this?

Thanks

Hoff

googlehoff · Jan 6th 2006

Re: Offset Vlookup

Domenic,

THANK YOU!

My lookup value is actually in B2 on the form and in column A on the base sheet, but i just switched that around and added a few little things for validation and it works great!

Thanks again,

Hoff

googlehoff · Jan 6th 2006

Re: Offset Vlookup

Each individual's accounts will always be consecutive.

googlehoff · Jan 6th 2006

Re: Offset Vlookup

I have attached an example of whatthe sheet looks like and what I am trying to accomplish.

Right now with TRUE and FALSE VLOOKUPS, I can get the first and last entries. I am looking for a way to get 2 and 3.

googlehoff · Jan 6th 2006

Re: Offset Vlookup

=cell("address",VLOOKUP($B$2,'Base Sheet'!$A$1:$Z$266,12,FALSE))

I tried that and returned an error in teh formula. is there another way?