Posts by jproffer

Re: VBA CODE for Excel 2007 & 2010

Quote

I would like for their to be a column for "Days Listed" (# of day listed) I am creating a column for the actual date they are put on market. We could add in a block for the new property entry that says "Date Listed" It would need to except the .... *mm/dd/yyyy format so it can update right?

It will accept the date format. No problem.

Quote

Good call on that lol... also I would like to add another block on the user form for adding new property that has "MLS#" and the column to be column E but I hope it is easy to change the codes to still work.

It won't be too bad, lol.

Re: VBA CODE for Excel 2007 & 2010

Also, something else to think about...

Do you want the user to continue using "days added" on the userform, or switch to "DATE added"?

makes little difference in the coding, it's just as well either way.

Re: copy cells automatically to next blank cells in another sheet

forum.ozgrid.com/index.php?attachment/44909/

Ok, try this out. It's made to stop if it runs out of room and warn you that all data was not copied.

Sub MoveSh1_to_Sh2()
Dim i As Long
Dim LR As Long
Dim MyCol As Integer
Dim MyRow As Integer
 
LR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
MyCol = 3
MyRow = 19
    For i = 1 To LR
        If Sheets("Sheet1").Range("A" & i).Value <> "" Then
            Sheets("Sheet2").cells(MyRow, MyCol).Value = Sheets("Sheet1").Range("A" & i).Value
            MyCol = MyCol + 5
                If MyCol = 23 Then
                    MyCol = 3
                    MyRow = MyRow + 1
                End If
            If MyRow = 39 Then
                MsgBox "You have ran out of room.  Some entries were not copied"
                Exit For
            End If
        End If
    Next i
End Sub

Re: VBA CODE for Excel 2007 & 2010

Yep, if you can post that after you're done I'll fix the code to suit. Some remote column...for all I care it can be in column XFD, lol...doesn't matter, but if it's way over there it will make your scroll bar very tiny.

But you get the idea...the column can be hidden/zero width or formatted with white text or however you want to hide them.

After the current properties are "dated" with the starting date, the rest after that can be automatic and the "days listed" can be a simple:

=Now() - that date

Anyhow, I ramble, lol....if you can make a column with starting dates...a column that will never be overwritten...I can fix ya up.

Re: copy cells automatically to next blank cells in another sheet

I'm sorry...maybe I'm dense, but I'm still not following. You have 120 cells in sheet 1 and you want to copy that into 21 cells (C19:W19) in sheet 2? Or, you want to copy the 120 cells in sheet 1 to 420 cells in sheet 2, which will leave empty cells, needless to say....?

I looked at your example, but sheet 2 is blank. Maybe if you put the desired results into sheet 2, even if it's manually this time.

Re: copy cells automatically to next blank cells in another sheet

I was with ya until this:

Quote

and continue into next empty cell until each cell down to W38 is filled

What does that mean? It's a fairly simple thing to copy an entire column to the next available column on another sheet....or to copy rows 19 through 138 into the next available column on another sheet..........but ya kinda lost me with the C19 through W38 thing.

Re: VBA CODE for Excel 2007 & 2010

Ok, first guess.......

On sheet 2, columns AB and AD do copy over and the formulas are there, but the results don't show up because the column is too narrow.

I didn't really pay much attention to that before...I figured the narrow columns were just blank columns for spacing.

The values are there...widen the columns if you want to see them

Re: VBA CODE for Excel 2007 & 2010

Which sheet are we talking about? And which operation? Adding a new property or copying a user choice over or copying all "good investments"?

The filling of the formulas happens for the entire row before the new property is posted. I'll look into the other 2 operations in the mean time and see what happens.

Re: Multiply a cell with a number but when there is no data show a zero

Naa, don't feel dumb

The PRODUCT function ignores blank values, which in multiplication speak means multiply by 1...the times operator "*" counts blank cells as zero. That's why the difference in answers.

Re: Multiply a cell with a number but when there is no data show a zero

Just use:

=I2 * 0.12

Re: Repopulate UserForm ListBox from CheckBox

What do you want to happen if both checkboxes are checked at the same time? Or do you want that to not be possible?

This might be something for you....just an extension/modification of Smallman's idea.

forum.ozgrid.com/index.php?attachment/44901/

Re: Update a cell when year ends

forum.ozgrid.com/index.php?attachment/44898/

I've made a few changes, but I'm still not sure if it's going to help you.

I made column F in invoice list to create numbers automatically based on the date in column E. Then column C takes the same number as column F and the invoice number on the "invoice" finds the last number in column C and adds 1 to it.

Let me know if that's even close, or if I'm way off...I'll try something else

Re: Update a cell when year ends

The example I gave was just a quick way to make a list. Instead of more guessing, maybe post your workbook so I can see how it's set up

Re: Update a cell when year ends

You could do it with a formula. This is obviously a very simple, quick example...but it could be modified for about anything.

forum.ozgrid.com/index.php?attachment/44896/

Re: VBA CODE for Excel 2007 & 2010

Not a problem at all. Glad to help

"much more" hahaha, that's funny. You're not bothering me though. If you need any other changes, let me know.

"[email protected]"[/email]

Re: VBA CODE for Excel 2007 & 2010

forum.ozgrid.com/index.php?attachment/44893/

Ok, this one should do all that. Still haven't added the automatic "days listed", because I don't have the original added dates, but that's a fairly simple affair.

If you want to add that, the code will have to be changed a bit though so if you have trouble let me know.

Don't be afraid to experiment either ....I keep everything I make in a file, so I'll have a backup (as you should as well, if your going to change things that are untested, not just on this workbook, but any workbook).

Anyhow, this one:
-On a new property add, will copy down the last entire row before posting the new property, then overwrite the appropriate columns with the new data.
-Has the requested box for advertising, and adds to column W.

If you have any other problems/changes needed let me know. Believe it or not, I enjoy trying to do new things in excel....guess I'm a nerd lol..............................but I can live with that.

Re: VBA CODE for Excel 2007 & 2010

Yet another update:

Some more thoughts:
-One way you could make the days listed update is to (in a remote column) add a date that is (days listed) days before NOW(), then put a formula in the Days Listed column subtracting that date from NOW(). If that interests you, you'll have to create the column and post the date posted for all the properties you have listed.
-All the tools are now in one userform. Try it out and see what you think.

Any other ideas, I'll give it a shot.

forum.ozgrid.com/index.php?attachment/44886/

Re: VBA CODE for Excel 2007 & 2010

Ok, this is a start.

Few notes:
-I deleted the old column G "Zestimate" and adjusted the Search code for that.
-Created a userform to add new properties to sheet 1. There is a button for that on sheets 1 AND 2, but it will only add new ones to sheet 1.
-As far as the formulas, you could drag those down as far as you want...the add property code doesn't affect those columns.
------If you don't want to see the "0"s, for instance in column H, you could use =IF(F4="","",F4*.85). I didn't do that yet, but it's a simple matter.
-The user input to copy will copy whatever address they type in FROM sheet1 TO sheet2.
- Haven't messed with getting the "days listed" to update daily yet, but I will when I get home tonight, if you haven't by then.
- On the userform, if you start in the address box, as you push enter it will go across then down. I.E.-to city, then state, zip, etc, etc. Bad tab order is very irritating, IMO, so I try to set it to work correctly, lol.
-I took a few liberties. For instance, I inserted a new row above the headers for buttons and such...and froze the header row on each sheet for easier readability.

Hope this is getting closer to what you want. We can tweak it some more to suit your needs as time goes on.

forum.ozgrid.com/index.php?attachment/44883/

Re: VBA CODE for Excel 2007 & 2010

LOL...I think I understand, but it sounds like a userform would be better. I'll see what I can come up with.

I do have another question though.

RE: The user input code:
Is this to copy something from one sheet to another, or is this to enter a totally new property?

Since the coding is already done to copy from one sheet to another, I'll work on the latter for now.

Re: VBA CODE for Excel 2007 & 2010

forum.ozgrid.com/index.php?attachment/44881/

This is an example of my last post.