PDA

View Full Version : [SOLVED] activecell.address function



vbid
07-12-2017, 01:21 AM
Range("A1").Select
Cells.find(what:="Direct business", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Select
If ActiveCell.find(what:=" 1 ", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False) Is Nothing Then
ActiveCell.Offset(1, 0).Select
ActiveCell = "=REPLACE(" & ActiveCell.Address & ",FIND("" 1 ""," & ActiveCell.Address & ",1),3,""*1*"")"
Else
ActiveCell = "=REPLACE(" & ActiveCell.Address & ",FIND("" 1 ""," & ActiveCell.Address & ",1),3,""*1*"")"
End If
ActiveCell.Copy
ActiveCell.PasteSpecial xlPasteValues


Hi readers, I have a problem with this code currently as activecell.address is always stuck at $A$1. Would seek help to change my code to read the correct activecell.address

mdmackillop
07-12-2017, 02:10 AM
What is your code intended to do?

vbid
07-12-2017, 02:19 AM
Currently i have a set of data with multiple columns that looks like this. It can be categorised into 3 columns. U can take a look at the data. I have already converted the data into text files.

Marine and Misc -
Marine and Work Injury Misc - Misc - Misc -
Row Aviation - Personal Misc - Credit / Misc - Misc - Sub-
Description Aviation - Fire Motor Compensati Health Public Engineering Professiona Total
No. Hull and Accident Bonds Political Others Total
Cargo on Liability / CAR / EAR l Indemnity
Liability Risk
A. PREMIUMS
Gross premiums
Direct business 1 0 0 0 0 0 0 19,641,695 0 0 0 0 0 0 0 19,641,695
Reinsurance business accepted -
In Singapore 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
From other ASEAN countries 3 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
From other countries 4 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Total (2 to 4) 5 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Reinsurance business ceded -
In Singapore 6 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
To other ASEAN countries 7 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
To other countries 8 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Total (6 to 8) 9 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Net premiums written (1 + 5 - 9) 10 0 0 0 0 0 0 19,641,695 0 0 0 0 0 0 0 19,641,695
Premium liabilities at beginning of period 11 0 0 0 0 0 0 9,845,368 0 0 0 0 0 0 0 9,845,368
Premium liabilities at end of period 12 0 0 0 0 0 0 7,465,343 0 0 0 0 0 0 0 7,465,343
Premiums earned during the period
13 0 0 0 0 0 0 22,021,720 0 0 0 0 0 0 0 22,021,720
(10 + 11 - 12)
B. CLAIMS
Gross claims settled
Direct business 14 0 0 0 0 0 0 16,380,748 0 0 0 0 0 0 0 16,380,748
Reinsurance business accepted -
In Singapore 15 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
From other ASEAN countries 16 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
From other countries 17 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Total (15 to 17) 18 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Recoveries from reinsurance
business ceded -
In Singapore 19 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
To other ASEAN countries 20 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
To other countries 21 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Total (19 to 21) 22 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Net claims settled (14 + 18 - 22) 23 0 0 0 0 0 0 16,380,748 0 0 0 0 0 0 0 16,380,748
Claims liabilities at end of period 24 0 0 0 0 0 0 7,927,116 0 0 0 0 0 0 0 7,927,116
Claims liabilities at beginning of period 25 0 0 0 0 0 0 5,730,453 0 0 0 0 0 0 0 5,730,453
Net claims incurred (23 + 24 - 25) 26 0 0 0 0 0 0 18,577,411 0 0 0 0 0 0 0 18,577,411
C. MANAGEMENT EXPENSES
Management Expenses 27 0 0 0 0 0 0 5,687,858 0 0 0 0 0 0 0 5,687,858
D. DISTRIBUTION EXPENSES
Commissions 28 0 0 0 0 0 0 2,198,246 0 0 0 0 0 0 0 2,198,246


Reinsurance commissions 29 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0


Net commissions incurred (28 - 29) 30 0 0 0 0 0 0 2,198,246 0 0 0 0 0 0 0 2,198,246
26/05/2017 10:07 AM
Net commissions incurred (28 - 29) 30 0 0 0 0 0 0 2,198,246 0 0 0 0 0 0 0 2,198,246


Other distribution expenses 31 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0


E. UNDERWRITING RESULTS
Underwriting gain / (loss) (13 - 26 -
32 0 0 0 0 0 0 (4,441,795) 0 0 0 0 0 0 0 (4,441,795)
27 - 30 - 31)
F. NET INVESTMENT INCOME 33 33 0 0 0 0 0 112,491 0 0 0 0 0 0 0 112,491


G. OPERATING RESULT (32 + 33) 34 0 0 0 0 0 0 (4,329,304) 0 0 0 0 0 0 0 (4,329,304)







I would need to add delimiters into row number e.g *1*,...,*34*. However i cannot just search and replace as u can see from description 33 that the data value also has a 33 in it. By adding the delimiters into the row number, i can seperate the worksheet into 3 portions. I have multiple sheets and hence i am unable to just specify the cell as the data format is slightly different for each spreadsheet. The only way i could do so is by searching for specific keywords and offset the cell value. The data I am trying to bring into excel can be found from this website: http://www.mas.gov.sg/Statistics/Insurance-Statistics/Insurance-Company-Returns.aspx

vbid
07-12-2017, 02:22 AM
Direct business *1* 0 0 0 0 0 0 19,641,695 0 0 0 0 0 0 0 19,641,695
Reinsurance business accepted -
In Singapore *2* 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
From other ASEAN countries *3* 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
From other countries *4* 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Total (2 to 4) *5* 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Reinsurance business ceded -
In Singapore *6* 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
To other ASEAN countries *7* 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
To other countries *8* 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Total (6 to 8) *9* 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Net premiums written (1 + 5 - 9) *10* 0 0 0 0 0 0 19,641,695 0 0 0 0 0 0 0 19,641,695
Premium liabilities at beginning of period 11 0 0 0 0 0 0 9,845,368 0 0 0 0 0 0 0 9,845,368
Premium liabilities at end of period 12 0 0 0 0 0 0 7,465,343 0 0 0 0 0 0 0 7,465,343
Premiums earned during the period
13 0 0 0 0 0 0 22,021,720 0 0 0 0 0 0 0 22,021,720
(10 + 11 - 12)
B. CLAIMS
Gross claims settled
Direct business 14 0 0 0 0 0 0 16,380,748 0 0 0 0 0 0 0 16,380,748
Reinsurance business accepted -
In Singapore 15 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
From other ASEAN countries 16 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
From other countries 17 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Total (15 to 17) 18 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Recoveries from reinsurance
business ceded -
In Singapore 19 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
To other ASEAN countries 20 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
To other countries 21 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Total (19 to 21) 22 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Net claims settled (14 + 18 - 22) 23 0 0 0 0 0 0 16,380,748 0 0 0 0 0 0 0 16,380,748
Claims liabilities at end of period 24 0 0 0 0 0 0 7,927,116 0 0 0 0 0 0 0 7,927,116
Claims liabilities at beginning of period 25 0 0 0 0 0 0 5,730,453 0 0 0 0 0 0 0 5,730,453
Net claims incurred (23 + 24 - 25) 26 0 0 0 0 0 0 18,577,411 0 0 0 0 0 0 0 18,577,411
C. MANAGEMENT EXPENSES
Management Expenses 27 0 0 0 0 0 0 5,687,858 0 0 0 0 0 0 0 5,687,858
D. DISTRIBUTION EXPENSES
Commissions 28 0 0 0 0 0 0 2,198,246 0 0 0 0 0 0 0 2,198,246


Reinsurance commissions 29 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0


Net commissions incurred (28 - 29) 30 0 0 0 0 0 0 2,198,246 0 0 0 0 0 0 0 2,198,246
26/05/2017 10:07 AM
Net commissions incurred (28 - 29) 30 0 0 0 0 0 0 2,198,246 0 0 0 0 0 0 0 2,198,246
Other distribution expenses 31 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
E. UNDERWRITING RESULTS
Underwriting gain / (loss) (13 - 26 -
32 0 0 0 0 0 0 (4,441,795) 0 0 0 0 0 0 0 (4,441,795)
27 - 30 - 31)
F. NET INVESTMENT INCOME 33 33 0 0 0 0 0 112,491 0 0 0 0 0 0 0 112,491
G. OPERATING RESULT (32 + 33) 34 0 0 0 0 0 0 (4,329,304) 0 0 0 0 0 0 0 (4,329,304)

I have delimited the first 10 results as an example. I would require the delimited data to range to 34. However, at 33, i cannot search and replace as it would delimit both the data value and the row number

mdmackillop
07-12-2017, 02:25 AM
I'll have a look.
Meantime, refering to activecell is problematic. I've adjusted your code to use C & D as range variables; this will show how you should refer to your cell addresses (althought this implementation is probably wrong)

Sub Test()
Dim c As Range, d As Range


Set c = Cells.Find(what:="Direct business", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)


Set d = Cells.Find(what:=" 1 ", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)


If c Is Nothing Then
c.Offset(1, 0).Select
Selection.Formula = "=REPLACE(" & c.Address & ",FIND("" 1 ""," & c.Address & ",1),3,""*1*"")"
Else
Selection.Formula = "=REPLACE(" & d.Address & ",FIND("" 1 ""," & d.Address & ",1),3,""*1*"")"
End If
Selection.Value = Selection.Value
End Sub

mdmackillop
07-12-2017, 02:35 AM
Please post sample workbook or txt file (you may need to zip it). Have you tried Excel's Data Import function?

vbid
07-12-2017, 06:11 PM
1972019721

I have 2 files currently. Basically, I would require to extract data from form 6 from the website stated. The original file does not work as the company Cardinal has 33 in its row number as well as its value. You would require a pdftotext.exe to convert the files to text files before the import button can work.

Basically, I would need the test file to work as the formula would only add *i* to the number where i is an integer. The difference in the formulas between the 2 files is in module 6.

vbid
07-12-2017, 06:12 PM
Thank you for your help

vbid
07-12-2017, 06:22 PM
The exe could be from here http://download.cnet.com/PDF-to-Text/3000-18497_4-75415960.html

vbid
07-12-2017, 08:29 PM
I have solved the issue. Thank you!