Reconfiguring variable assignments

maverick0987

New Member
Joined
Apr 15, 2013
Messages
6
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
frown.gif
. 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
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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.
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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

New Member
Joined
Apr 15, 2013
Messages
6

ADVERTISEMENT

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

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
maverick0987,

Can we have another workbook without any hidden worksheets.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,683
Messages
5,597,526
Members
414,152
Latest member
ReservoirDodds

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top