Repository navigation
Expand file tree
/
Copy pathlast_things.vb
More file actions
90 lines (66 loc) · 2.77 KB
/
Copy pathlast_things.vb
File metadata and controls
90 lines (66 loc) · 2.77 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
Function last_col(Optional str_sheet As String, Optional row_to_check As Long = 1) As Long
Dim shSheet As Worksheet
If str_sheet = vbNullString Then
Set shSheet = ActiveSheet
Else
Set shSheet = Worksheets(str_sheet)
End If
last_col = shSheet.Cells(row_to_check, shSheet.Columns.Count).End(xlToLeft).Column
End Function
Function last_row(Optional str_sheet As String, Optional column_to_check As Long = 1) As Long
Dim shSheet As Worksheet
If str_sheet = vbNullString Then
Set shSheet = ActiveSheet
Else
Set shSheet = Worksheets(str_sheet)
End If
last_row = shSheet.Cells(shSheet.Rows.Count, column_to_check).End(xlUp).Row
End Function
Public Function l_locate_value_row(target As String, ByRef target_sheet As Worksheet, _
Optional l_col As Long = 1, _
Optional l_more_values_found As Long = 1, _
Optional b_look_for_part = False) As Long
Dim l_values_found As Long
Dim r_local_range As Range
Dim my_cell As Range
l_values_found = l_more_values_found
Set r_local_range = target_sheet.Range(target_sheet.Cells(1, l_col), target_sheet.Cells(Rows.Count, l_col))
For Each my_cell In r_local_range
'The b_look_for_part is for the vertriebscase
If b_look_for_part Then
If target = Left(my_cell, Len(target)) Then
If l_values_found = 1 Then
l_locate_value_row = my_cell.Row
Exit Function
Else
Call Decrement(l_values_found)
End If
End If
Else
If target = Trim(my_cell) Then
If l_values_found = 1 Then
l_locate_value_row = my_cell.Row
Exit Function
Else
Call Decrement(l_values_found)
End If
End If
End If
Next my_cell
l_locate_value_row = -1
End Function
Public Function l_locate_value_col(target As String, _
ByRef target_sheet As Worksheet, _
Optional l_row As Long = 1)
Dim cell_to_find As Range
Dim r_local_range As Range
Dim my_cell As Range
Set r_local_range = target_sheet.Range(target_sheet.Cells(l_row, 1), target_sheet.Cells(l_row, Columns.Count))
For Each my_cell In r_local_range
If target = Trim(my_cell) Then
l_locate_value_col = my_cell.Column
Exit Function
End If
Next my_cell
l_locate_value_col = -1
End Function