Reconfiguring variable assignments

[maverick0987]

Greetings,

6 months ago someone here helped me build a macro to reformat a report. I own a property management company and I use this report to list my current vacancies. The way it is generated through my management software is very difficult to read and is poorly designed. After installing an update to our management software it changed the look and cell placement of the report and therefore my macro will not work

. I have tried changing the cell assignments for the variables but some of the coding was a little over my head. I have included the code for the macro here. Any help or advice would be greatly appreciated! Thank you!

-J​

P.S. I've done some playing around with it. The new report has cells that are merged, how do I copy the text in those cells into my output sheet? It says #Value the way Ive tried!

Sub aaa()
Dim OutSH As Worksheet
cntr = 0
Set OutSH = Sheets.Add(after:=Sheets(1))
ActiveSheet.Name = "Output"
OutSH.Range("A1").Value = "UNIT AVAILABILITY FOR RENT MAXIMIZER PRICING MODEL"
Range("G1").Formula = "=today()"

Sheets(1).Activate


For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
If Left(Cells(i, 2), 5) = "Unit:" Then unit = Right(Cells(i, 4), Len(Cells(i, 1)) - 6)
If Left(Cells(i, 1), 10) = "Unit Type:" Then unittype = Right(Cells(i, 1), Len(Cells(i, 1)) - 11)
If Left(Cells(i, 1), 12) = "Market Rent:" Then mktrent = Right(Cells(i, 1), Len(Cells(i, 1)) - 13)
If Left(Cells(i, 1), 12) = "Unit Status:" Then unitstatus = Right(Cells(i, 1), Len(Cells(i, 1)) - 13)
If Left(Cells(i, 1), 11) = "Date Ready:" Then dateready = Right(Cells(i, 1), Len(Cells(i, 1)) - 12)
If Cells(i, 1) = "Lease Term" Then
outrow = OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(2, 0).Row
OutSH.Cells(outrow, 1).Value = "Unit Type:"
OutSH.Cells(outrow, 1).Font.Bold = True
OutSH.Cells(outrow, 2).Value = unittype
OutSH.Cells(outrow, 2).Font.Bold = True
OutSH.Cells(outrow + 1, 1).Value = unit
OutSH.Cells(outrow + 1, 3).Value = "Market Rent:"
OutSH.Cells(outrow + 1, 3).Font.Underline = True
OutSH.Cells(outrow + 1, 4).Value = mktrent
OutSH.Cells(outrow + 1, 5).Value = "Unit Status:"
OutSH.Cells(outrow + 1, 5).Font.Underline = True
OutSH.Cells(outrow + 1, 6).Value = unitstatus
OutSH.Cells(outrow + 1, 7).Value = "Date Ready:"
OutSH.Cells(outrow + 1, 7).Font.Underline = True
OutSH.Cells(outrow + 1, 8).Value = dateready
Cells(i, 1).Resize(15, 5).Copy Destination:=OutSH.Cells(outrow + 2, 1)
cntr = cntr + 1
If cntr = 2 Then
OutSH.HPageBreaks.Add Before:=OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
cntr = 0
End If

With OutSH.Cells(outrow + 1, 4).Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With OutSH.Cells(outrow + 1, 6).Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With OutSH.Cells(outrow + 1, 8).Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
End If

Next i
OutSH.Columns("B:E").AutoFit
OutSH.Columns("F:F").ColumnWidth = 14.4
OutSH.Columns("G:H").AutoFit
OutSH.Columns("A:A").ColumnWidth = 14.25
OutSH.Rows("1:10000").RowHeight = 13.75
OutSH.PageSetup.Orientation = xlLandscape
OutSH.Columns("A:H").HorizontalAlignment = xlCenter
OutSH.Range("A1").HorizontalAlignment = xlLeft

End Sub

 

[hiker95]

maverick0987,

Welcome to the MrExcel forum.

When posting VBA code, please use Code Tags - like this:

[code]

'Paste your code here.

[/code]


What version of Excel are you using?

Can you post a screenshot of the raw data worksheet, and, post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker


Even better for your request:

If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data scrubbed/removed/changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[maverick0987]

Thank you for your help...I have included the 2 photos. I am using excel 2010.https://www.box.com/s/a7vmmup8ub8cdngtzjbl -Jamie

 

[hiker95]

maverick0987,

Thanks for the workbook.

I do not see the correlation from Raw Data to worksheet Visual Representation?

I can not find worksheet Visual Representation Unit Type: 12705-20 information on worksheet Raw Data??

I would think that you should use the information from Raw Data, and, manually complete Visual Representation in order for someone to understand what information is going where.

And, worksheet Instructions contains pictures/graphics that are difficult to see and understand.

Please post another workbook.

 

[maverick0987]

Hiker95,

My apologies, I've sent you the wrong version. I have included the link to the correct version. The 'instructions' tab was for the old VBA module which should still be in there. This was merely for my employees as a reference on how to operate the workbook. Refer to that module though for a benchmark. It will have to be set in a loop because the report is always a different length based on how many apartments are vacant at a given time. To give you a background on my problem, the 'raw data' report prints datasets half on one pg half on the other making it difficult to read and contains information that is quite frankly unneccessary for our day-to-day reference and creates confusion. That being said, if you could help me with the loop and getting the data to copy in the way I have demonstrated, I can take care of the bold font, auto-fit columns, underlining, etc.

One other question...is there a function I can apply to the 'raw data' to require the user to only be able to paste 'values and number formatting' only? This was the only way I could keep merged cells out so that I could properly reference the values I needed.

Thank you VERY much for your help & patience.

Cheers,

JRob

Link:
https://www.box.com/s/xops6mkf0ckjt3ounwj7

 

[hiker95]

maverick0987,

Can we have another workbook without any hidden worksheets.

 

[maverick0987]

https://www.box.com/s/xops6mkf0ckjt3ounwj7 Here is the link,

Thanks,

-J

 

[NoSparks]

maverick0987, with the file you posted at both

Reconfiguring variable assignments

Reconfiguring variable assignments

it would appear you are after page 2 of https://www.box.com/s/xops6mkf0ckjt3ounwj7

Is this correct?

 

[hiker95]

maverick0987,

A message to forum cross posters
Please read this
Excelguru Help Site - A message to forum cross posters


Click on the Reply to Thread button, and just put the word BUMP in the post. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[maverick0987]

maverick0987, with the file you posted at both

Reconfiguring variable assignments

Reconfiguring variable assignments

it would appear you are after page 2 of https://www.box.com/s/xops6mkf0ckjt3ounwj7

Is this correct?

Yes, that is exactly what I need In terms of the finished product, just a simple formatting change.

Thank You,

-J